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?

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Dec 26, 10:23am, noumian <n.nou...@gmail.com> wrote:
> 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?

SELECT *, Incident.incident_name
FROM action LEFT OUTER JOIN
Incident ON action.incident_id =
Incident.incident_id
WHERE (action.dept_id = 3)

On Wed, 26 Dec 2007 04:27:05 -0800 (PST), "harry9katz@gmail.com"
<harry9katz@gmail.com> wrote:

>On Dec 26, 10:23am, noumian <n.nou...@gmail.com> wrote:
>> 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?
>
>SELECT *, Incident.incident_name
>FROM action LEFT OUTER JOIN
> Incident ON action.incident_id =
>Incident.incident_id
>WHERE (action.dept_id = 3)

That selects where some of the action took place in department 3.

Try

select incident.incident_id, incident_name
from incident inner join action on incident.incident_id =
action.incident_id
group by incident.incident_id, incident_name
where max(dept_id) = 3 and min(dept_id) =3

This presumes dept_id is always filled in.