Help With Complicated Query - 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

Help With Complicated Query

Author

Topic

boybles
Starting Member

23 Posts

Posted - 2011-11-03 : 23:50:23

I'm trying to join the following tables such that the resulting query will display all of the EmployeeIDs and the HobbyIDs that they DON'T have. Please refer to the following and advise. I'm completely stumped. Thanks!!!
Tony

TABLES
============

Employee
-------------------
EmployeeID (Number 3)
EmployeeName (Varchar2 25)

Hobby
-------------------
HobbyID (Number 3)
HobbyName (Varchar2 25)

EmployeeHobbyMap
-------------------
EmployeeHobbyMapID (Number 3)
EmployeeID (Number 3)
HobbyID (Number 3)

DATA
==========

Employee
-----------------------
1| John Smith
2| Jane Washington
3| Tom White
4| Ken James

Hobby
-------------------------
1| Fishing
2| Tennis
3| Soccer
4| Gardening

EmployeeHobbyMap
-------------------
1|1|2
2|1|3
3|1|4
4|2|1
5|2|3
6|3|1
7|3|2
8|3|3
9|3|4
10|4|2

DESIRED RESULTS
===============
EmployeeID|HobbyID
------------------
1|1
2|2
2|4
4|1
4|3
4|4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-04 : 00:33:39

[code]
select e.EmployeeID, h.HobbyID
from Employee e
cross join Hobby h
where not exists
(
select *
from EmployeeHobbyMap eh
where eh.EmployeeID = e.EmployeeID
and eh.HobbyID = h.HobbyID
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

boybles
Starting Member

23 Posts

Posted - 2011-11-04 : 02:25:56

Thank you, Khtan. That is exactly what I was looking for...and it really put me on the right track. I have a couple other things I need to work out...but this puts me on the right track. Thanks again for your invaluable help.
Tony [:-)]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-04 : 02:30:11

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources