Posted by Erland Sommarskog on 12/26/07 09:21

noumian (n.noumia@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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: M`I,5.Persecut ion ` Molesta tion durin g Travel
• Prev in forum: M.I'5-Persecu tion . BBC N ewscasters Li e & Den y They re W atching Me
• Thread view: Re: just to do a select

[Reply to this message]