Re: Show missing records

[Ramalingam Chelliah]

Hi,
Use Left Outer Join for getting results as how you expect them to be...

Tables are joined based on Join Condition ...
If you join tables with condition
ON (backorders.lineID <> items.ITEM_ID)
Then they will be paired with this condition

Say T1 T2 are tables

T1

F1 F2
1 Hai
2 Try
3 Why
4 Sky

T2 is subset(subtable) of T1

( mysql> CREATE TABLE T2 AS
SELECT * FROM T1 WHERE T1.ID<3 ;)

T2

F1 F2
1 Hai
2 Try


Now as per given condition
T1(1 ,3,4) will be paired with T2(2) --only 2 is exempted
T1(2,3,4) will be paired with T2(1) --only 1 is exempted



Now the right solution

mysql> SELECT items.QUANTB,items.ITEM_ID,backorders.lineID
FROM
items
LEFT OUTER JOIN backorders
ON items.ITEM_ID = backorders.lineID
WHERE backorders.ID IS NULL
AND items.QUANTB > 0
group by
items.ITEM_ID ;

Hope this helps you....

Regards,
Ram.








We Learn the Most When we have to Invent