Re: Selecting item list and count from other table

[Ramalingam Chelliah]

Hi,
If you use INNER JOIN ( comma separated tables) then you won't get
entries for those fields which could not be paired ...
If a container has no item then it won't have an entry int items table...

So Use LEFT OUTER JOIN

SELECT X.key_no,COUNT(Y.key_no) AS 'ItemCount'
FROM
containers as X
LEFT OUTER JOIN
items as Y
ON
X.key_no=Y.cont_key_no
AND X.status='OPEN"
GROUP BY X.key_no;

Hope this helps you....

Regards,
Ram.


We Learn the Most When we have to Invent