471,090 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

just to do a select

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?
Dec 26 '07 #1
3 1232
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
Dec 26 '07 #2
On Dec 26, 10:23*am, noumian <n.nou...@gmail.comwrote:
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)
Dec 26 '07 #3
On Wed, 26 Dec 2007 04:27:05 -0800 (PST), "ha********@gmail.com"
<ha********@gmail.comwrote:
>On Dec 26, 10:23*am, noumian <n.nou...@gmail.comwrote:
>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.
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mr_burns | last post: by
9 posts views Thread by Leszek | last post: by
66 posts views Thread by mensanator | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.