June 28, 2003 at 5:49 am
how can i count and return zero
if thre no count return ZERO
------------
SELECT COUNT(*) AS coun_id, id
FROM dbo.Sn
GROUP BY id
HAVING (id IN
(SELECT id
FROM dbo.SilokE))
---------------------------------
thnks
ilan
June 28, 2003 at 9:41 am
Not sure that i have fully understood the question, becuase it will be difficult to count that which does not exist. If you want a list of records that does not exist in a referenced table, then you could look at a left join.
eg. In northwind to prove the customers that have not placed any orders you could:
select c.companyname,o.orderid
from customers c left join orders o on c.customerid = o.customerid
where o.orderid is null
..Should return two rows. Hope this helps.
June 29, 2003 at 2:45 pm
As J.O.S. was suggesting
SELECT Sn.[id],COUNT(*) AS coun_id
FROM Sn RIGHT JOIN SilokE ON sn.[id]=SilokE.[id]
Where sn.[id] IS NULL
GROUP BY SilokE.[id]
For all remove the where clause.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply