Query Help

Anon

I have two tables "Products" and "Descriptions". "Products" contains a unique Product ID (PID) and other Product Information. "Descriptions" contains the unique product ID and also a description. The description corresponds to the unique product ID. I can't formulate a query where I can SELECT all records in "Products" that DO NOT have a corresponding description in "Descriptions". The best I could come up with is
//
SELECT Products.PID FROM Products, Descriptions WHERE Products.PID != Descriptions.PID
//
But this just returns all the rows in both tables multiplied together that do not have a Description...Arggghh this is frustrating.
If anyone could post a working query to solve this problem it would be greatly appreciated.

chowdary

Hi,
I am not master in SQL so,take it easy if the solution i am suggesting is not a proper solution

What my suggetion is use the LEFT JOIN to join the tables.If you use the LEFT JOIN you will get all rows from the first table(Products) regardless whether there is match in the second(Description) table and you will get NULL if you are taking some field from the second table.

Here is the scenario

Products
PID PRICE
p1 10.00
p2 12.00
p3 11.00

Description

PID DESC
p1 blah blah
p3 blah blah

Now if you use this query

$qry="SELECT A.PID,A.PRICE,B.DESC FROM Products as A LEFT JOIN Decription as B ON A.PID=B.PID";

Your result set will be

PID PRICE DESC
p1 10.00 blah blah
p2 12.00 NULL
p3 11.00 blah blah

If you notice the value of DESC in the result for product p1 is NULL so, check the value of DESC and display the record only if DESC in NULL

Cheers
Ajay