QUERY Help - Crossing Data on 2 Tables

[Canman2005]

Hi all

 

Really really need someones help.

 

I am having an issue puttrng a QUERY togther.

 

Let me firstly explain my tables, I have two tables called "products" and also "linked_products", I have put a brief export below

 

 

products

 

ID    TITLE

1    chips

2    burgers

3    drinks

 

 

linked_products

 

ID  PRODUCT_ID  TYPE

1    2                  3

2    2                  4

3    1                  3

 

 

So as you can see, there is a field called "PRODUCT_ID" in the table called "linked_products", that field refers to the ID field held in "products" table.

 

As you will also see, row 2 in the "products" table (burgers) is listed twice in the "linked_products" table assigned to "TYPE" 3 and 4.

 

Product ID 1 (chips) is assigned to "TYPE" 6 in the "linked_products" table.

 

You will notice that ID 3 of the "products" table (drinks) is now listed in the "linked_products" table at all.

 

What I need to do is run a QUERY which gets all rows from the "products" table excluding any rows which are listed under the "PRODUCT_ID" field in the "linked_products" that have a "TYPE" equaling 3 for example, not returning any with "TYPE" defined as 3 would just return ROW 3 (drinks), this is because ROW 3 (drinks) does not appear in the "linked_products" table with a value of 3.

 

If we were to do it again but asking for any which have a "TYPE" as 4, then it would return just ROW 1 (chips) and 3 (drinks) this is because ROW 1 and ROW 3 of the "products" table don't have a value of 4 under the "TYPE" field in the "linked_products" table, only ROW 2 has a field value of 4.

 

Tried to explain this the best I can, if anyone can help, that would be fantastic as im hitting my head harder ansd harder lol

 

Thanks

 

Ed

[JonnoTheDev]

SELECT p.ID, p.TITLE, l.TYPE FROM products p LEFT JOIN linked_products l ON (p.ID = l.PRODUCT_ID AND l.TYPE !='3') ORDER BY p.TITLE ASC