| Author |
 Topic  |
|
|
105ben
Starting Member
United Kingdom
16 Posts |
 Posted - 01/02/2013 : 17:31:39
|
Hello,
I need to write a query that will look in a table and return the user ID of a person who has been allocated for all of the departments. The tables look like:
PERSON(person_id, person_name)
ALLOCATION(alloc_id, person_id, dep_id, date_start, date_end)
DEPARTMENT(dep_id, dep_name)
There are 4 departments, with ID's of 1,2,3 and 4. I cannot use a count, as the person may quit a dept. then work for it again meaning the count will return 2, when theyve only really worked for 1...
Does anybody know how I would go about this? |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/02/2013 : 17:54:32
|
SELECT p.Person_id,p.person_name
FROM person p
INNER JOIN ALLOCATION a on p.person_id = a.person_id
INNER JOIN DEPARTMENT d on a.dep_id = d.dep_id
GROUP BY p.Person_id,p.person_name
HAVING COUNT(distinct dep_id) = 4
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
105ben
Starting Member
United Kingdom
16 Posts |
Posted - 01/02/2013 : 18:05:49
|
Thanks Jim! Appreciate that!
How come both person_id and person_name are in the group by column, just out of curiosity? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/03/2013 : 00:51:26
|
if you want to select both of columns they both need to be in GROUP BY. It doesnt cause any problem as person_id to person_name relationship would always be 1 to 1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Query to find multiple records
