noumian (n.noumia [at] gmail.com) writes:
> hello, i cant find how to make this select :
>
> here is what i have : 2 tables
> Incident(incident_id,incident_name)
> action(action_id,incident_id,action_name,dept_id)
>
> what i want?
> i would like to find all those incident which have all their action
> with dept_id=3.
>
> how can we do this?
Two ways to skin the cat:
SELECT i.incident_id, i.incident_name
FROM incidents i
WHERE EXISTS (SELECT *
FROM actions a
WHERE a.incident_id = i.incident_id)
AND NOT EXISTS (SELECT *
FROM actions a
WHERE a.incident_id = i.incident_id
AND a.dept_id = 3)
SELECT i.incident_id, i.incident_name
FROM incidents i
JOIN (SELECT incident_id
FROM actions
GROUP incident_id
HAVING COUNT(*) =
SUM(CASE WHEN dept_id = 3 THEN 1 ELSE 0 END)) AS a
ON a.incident_id = i.incident_id
--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
