SQL Query to select count = 0

Question

OK, I need a T-SQL guru for this one.

I have a table called PackageStoreEvents that has a packageid, storecode and an eventcode field. I want to return the packageid values for storecode 2406 where the count for the eventcode = 6 is 0.

How would I do that?

I'm stuck on this one.

Thanks for the help

Answer 1

This is not wildly pretty, but should work. You left join to the same table including the eventcode in the join condition, assert that the right table has no rows with IS NULL, and then do a DISTINCT to avoid the many rows that would otherwise be returned.

SELECT  DISTINCT p1.packageid
FROM    PackageStoreEvents p1
        LEFT JOIN
                PackageStoreEvents p2
                ON p1.packageid = p2.packageid
                AND p1.storecode = p2.storecode
                AND p2.eventcode = 6
WHERE p2.packageid IS NULL
AND   p1.storecode = 2406

Answer 2

Maybe this?:

SELECT PackageID
FROM PackageStoreEvents
WHERE Storecode = 2406 and EventCode = 6
GROUP BY PackageID 
HAVING COUNT(EventCode) = 0

Answer 3

SELECT packageid
FROM 
PackageStoreEvents p1
WHERE storecode = 2406
AND NOT EXISTS (SELECT * FROM PackageStoreEvents p2
                 WHERE p1.packageid = p2.packageid
                AND p1.storecode = p2.storecode
                AND p2.eventcode = 6)

Answer 4

Assuming I understand the question correctly I would use something along the lines of this :

-- PackageStoreEvents (packageid, storecode, eventcode)

SELECT DISTINCT packageid
  FROM PackageStoreEvents r
 WHERE storecode = 2406
   AND NOT EXISTS ( SELECT *
                      FROM PackageStoreEvents cnt
                     WHERE cnt.packageid = r.packageid
                       AND cnt.storecode = r.storecode
                       AND cnt.eventcode = 6)

Answer 5

Try:

select packageid 
from PackageStoreEvents
where storecode = 2406
group by packageid
having sum(case eventcode when 6 then 1 else 0 end)=0;

for a single-pass query.