just to do a select

[noumian]

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?

[Erland Sommarskog]

noumian (n.******@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, es****@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

[harry9katz]

On Dec 26, 10:23*am, noumian <[e-mail]:

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)

[Iain Sharp]

On Wed, 26 Dec 2007 04:27:05 -0800 (PST), "ha********@gmail.com"
<ha********@gmail.comwrote:

>On Dec 26, 10:23*am, noumian <[e-mail]:

>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_i d)

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.