nested selects from multiple tables and map tables

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

nested selects from multiple tables and map tables

Author

Topic

jonahpup
Starting Member

4 Posts

Posted - 2010-09-23 : 16:46:55

Hi...

Here is a basic rundown on my tables and how they are setup

Dog Table
Id int, Not Null, Primary Key
DogName nvarchar(50)

Person Table
Id int, Not Null, Primary Key
FirstName nvarchar(50)
LastName nvarchar(50)

DogPersonMap Table
DogId int, Not Null, Primary Key
PersonId int, Not Null, Primary Key

Event Table
Id int, Not Null, Primary Key
Name nvarchar(50)

DogEventMap Table
DogId int, Not Null, Primary Key
EventId int, Not Null, Primary Key

What to be able to get the following results:

Dog - Owners - Events
Abbey - Chris, Suzan, Bob - Jumping, Swimming, Eating
King - Chris, Robbie - Jumping, Swimming, Eating
Rufus - Suzan, Robbie - Swimming, Eating, Sleeping

but I have absolutely no idea how to do this in sql, I am using sqlserver 2008.
can anyone help me?? PLEASE!!

Cheers
Chris

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-23 : 17:03:15

Try this:

select d.DogName Dog, 
Stuff((	select ', '+P.FirstName 
	from Person P 
	inner join DogPersonMap M on M.PersonID=P.ID
	where M.DogID=D.ID
	FOR XML PATH('')),1,2,'') Owners,
Stuff((	select ', '+E.Name 
	from Event E 
	inner join DogEventMap M on M.EventID=E.ID
	where M.DogID=D.ID
	FOR XML PATH('')),1,2,'') Events
from Dog D

jonahpup
Starting Member

4 Posts

Posted - 2010-09-23 : 18:00:41

Thank you so much... works like a dream... that gives me a basis to work from...

jonahpup
Starting Member

4 Posts

Posted - 2010-09-23 : 19:54:18

I have added to this code to get all the data I require, however, I am getting duplicate results for some of the events... not sure why??

eg: Some dogs are showing the same event twice in the 'Events' output even though they only appear once in the database... something to do with the inner join??

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-23 : 20:07:15

try just adding
select distinct d.DogName Dog, etc.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources