Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
105ben
Starting Member
16 Posts |
Posted - 2013-01-02 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-02 : 17:54:32
|
SELECT p.Person_id,p.person_nameFROM person pINNER JOIN ALLOCATION a on p.person_id = a.person_idINNER JOIN DEPARTMENT d on a.dep_id = d.dep_idGROUP BY p.Person_id,p.person_nameHAVING COUNT(distinct dep_id) = 4JimEveryday I learn something that somebody else already knew |
|
|
105ben
Starting Member
16 Posts |
Posted - 2013-01-02 : 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
52326 Posts |
Posted - 2013-01-03 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|