[RESOLVED] SQL Query problem using != not excluding the condition across multiple tables

mauricejatdk-me

Hi,
I'm obviously missing something obvious here, so please excuse my ignorance, all help gratefully appreciated.

I'm working on a small stock management application. I have two tables in Mysql (Products & Products_Extra) Products contains the info we get from the supplier colour, size, barcode (which is the primary key for the table), Products_Extra is a table that contains our pricing info, shop category and the primary key is called FkeyBarcode.

I'm trying to find all the records in the Products table that are NOT in the Products_Extra table so that I can create a web page that the end user can then enter that information for multiple items at once based on say brand or productcode (e.g. one style of shoe may have several barcodes one for each size)

I know I'm going on a bit but I want to give you as much info as possible. So to the problem:

This query, rightly gives me the info for products that already have an entry in the Product_Extra table:

SELECT DISTINCT Products.Barcode, Products.StyleCode, Products.Size1, Products.Size2, Products.StyleDescription, Products.ColourDescription, Products.Brand FROM Products, Product_Extra WHERE Products.Barcode IN ('885290355470','885290355456') AND Products.Barcode = Product_Extra.FkeyBarcode

Which is the one entry from the two barcodes I do NOT want. So I try to exclude that barcode:

I know get the item I'm looking for but I'm still getting 885290355470 item which already has an entry in the product_extra table

As I said this is probably really obvious, but I'm just not getting it 🙂

Please help

Regards
Maurice

Weedpacket

mauricejatdk-me wrote:
Which is the one entry from the two barcodes I do NOT want.

It sounds like your criteria are back to front, then. Given

Products.Barcode IN ('885290355470','885290355456') AND Products.Barcode = Product_Extra.FkeyBarcode

the negation would be

Products.Barcode NOT IN ('885290355470','885290355456') OR Products.Barcode != Product_Extra.FkeyBarcode

That second set of criteria would select every record that the first set would have rejected.

mauricejatdk-me

Hi,
Thank you for the reply which after reading I tried:

SELECT DISTINCT Products.Barcode, Products.StyleCode, Products.Size1, Products.Size2, Products.StyleDescription, Products.ColourDescription, Products.Brand
FROM Products, Product_Extra
WHERE Products.StyleCode = 'A191956F' AND Products.Barcode NOT IN ('885290355470','885290355456')

Which does result in bring all the records for a given style of product that are NOT in the second table.

So I have to perform 2 queries one to find the barcodes, Then uses that result in the NOT IN('result first queries') in my second query to find the barcodes of the items I actually wanted.

Is there a way I can combine the 2 queries in one statement? I can easily use PHP to leave the result in a variable that the second query can use.

Anyway Thank you for the point in the right direction

mauricejatdk-me

Hmm to recap:

This SQL correctly identifies the entries in Table Products that also have an entry in the table Product_Extra.

SELECT Products.Barcode AS done FROM Products, Product_Extra WHERE Products.StyleCode = 'A191956F' AND Products.Barcode =Product_Extra.FkeyBarcode

| done |
| 885290355463 |
| 885290355470 |
Number of Results: 2

If I now run this query, I get all (six in this case) the entries from the table Products that do not have an entry in Product_Extra

SELECT DISTINCT Products.Barcode, Products.StyleCode, Products.Size1, Products.Size2, Products.StyleDescription, Products.ColourDescription, Products.Brand FROM Products, Product_Extra WHERE Products.StyleCode = 'A191956F' AND Products.Barcode NOT IN ('885290355463' , '885290355470')

| Barcode | StyleCode | Size1 | Size2 | StyleDescription | ColourDescription | Brand |
| 885290355456 | A191956F | 34 | 34 | ERGO JEAN | VINTAGE | NULL |
| 885290355425 | A191956F | 32 | 34 | ERGO JEAN | VINTAGE | NULL |
| 885290355418 | A191956F | 32 | 33 | ERGO JEAN | VINTAGE | NULL |
| 885290355401 | A191956F | 32 | 32 | ERGO JEAN | VINTAGE | NULL |
| 885290355395 | A191956F | 30 | 31 | ERGO JEAN | VINTAGE | NULL |
| 885290355388 | A191956F | 30 | 30 | ERGO JEAN | VINTAGE | NULL |
Number of Results: 6

Fantastic!
So I have tried to combine the two queries in one but nothing happens. The Mysql server doesn't kick back an error or any results it just appears stuck in an infinite loop.

My Nested SQL:
SELECT DISTINCT Products.Barcode, Products.StyleCode, Products.Size1, Products.Size2, Products.StyleDescription, Products.ColourDescription, Products.Brand
FROM Products, Product_Extra
WHERE Products.StyleCode = 'A191956F' AND Products.Barcode NOT IN (
SELECT Products.Barcode AS Done
FROM Products, Product_Extra
WHERE Products.StyleCode = 'A191956F' AND Products.Barcode =Product_Extra.FkeyBarcode
)

Again all help gratefully appreciated.

Ah after a little more research I think my nested SQL is actually OK but the version of Mysql on the server is 3.3 and doesn't support.

Thank you for the help, how do I flag this as resolved?
Maurice

Weedpacket

mauricejatdk-me wrote:
but the version of Mysql on the server is 3.3 and doesn't support.

Ouch, MySQL hasn't even supported that for more than two years 🙁

Thank you for the help, how do I flag this as resolved?

It's one of the options under the "Thread Tools" menu at the top of the thread.