SQL Join Help Needed - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Join Help Needed

Author

Topic

blakes
Starting Member

1 Post

Posted - 2009-10-30 : 17:45:55

Hello, new to the forums!

I am needing some assistance on how one would write the following SQL correctly so that based on the queried tasks, only the one-to-one matches would return? Example Table:

table:names
id | name
1 | Tom
2 | Jack
3 | Jill
4 | Bud

table:tasks
id | task
1 | Clean
2 | Security
3 | Manage
4 | Write
5 | Counter
6 | Stock

table:duty
taskid | nameid
1 | 1
1 | 3
2 | 1
2 | 2
3 | 2
3 | 3
3 | 4
6 | 1
6 | 4
6 | 3

I am trying to find out WHO can "Clean"/"Stock". The answer comes back 1-Tom 3-Jill 4-Bud. I am trying to eliminate 4-Bud since he is only "Stock" and not under "Clean"? Hopefully this makes sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 01:28:22

ok. here we go


SELECT n.id,n.name
FROM names n
INNER JOIN (SELECT d.nameid
            FROM duty d
            INNER JOIN tasks t
            ON t.taskid=d.taskid
            WHERE t.task IN ('Clean','Stock')
            GROUP BY d.nameid
            HAVING COUNT(DISTINCT taskid)=2
            )t
ON t.nameid=n.nameid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources