Advice re: Multi-Table Select - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Advice re: Multi-Table Select

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2006-02-27 : 18:43:15

I've built a sample database of family members and their anniversaries. The member information is stored in Members, the anniversary (and a description) is stored in Couples, and the members that make up each couple are stored in CoupleMembers.

I would like to have a Select statement (or stored proc) return a set of records containing each family member (Members.ID, .name, etc), their wedding date (Couples.WedDate), as well as their spouse (Members.ID, name, etc).

My guess is that I would have to look for any MemberID(2) in CoupleMembers where the CoupleID for MemberID(1) is the same, but where MemberID(2) <> MemberID(1).

Can somebody give me some tips on which approach makes the most sense?

I know there are easier ways to set up these tables, but I also know there should be a way to accomplish this with the tables as-is.

TABLE: Members
ID  Name
1   Albert Englebert
2   Jonathan Smith
3   Eleanor Englebert
4   Ruth Lincoln-Smith

TABLE: Couples
ID   WEDDATE      DESCRIPTION
10   2001/05/23   Al & Eleanor across the street
11   2003/04/12   John & Ruth from the country club

TABLE: CoupleMembers
CoupleID  MemberID
10          1
10          3
11          2
11          4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-27 : 20:03:34

the idea is basically just use table alias and inner join back itself

select 	m.ID as Member_ID, m.Name as Member_Name, 
	cms.MemberID as Spouce_ID, s.Name as Spouce_Name,
	cmo.CoupleID, c.WEDDATE, c.DESCRIPTION
from	#Members m
	inner join #CoupleMembers cmo
	on	m.ID	= cmo.MemberID
	inner join #CoupleMembers cms
	on	cmo.CoupleID	= cms.CoupleID
	and	cmo.MemberID	<> cms.MemberID
	inner join #Members s
	on	cms.MemberID	= s.ID
	inner join #Couples c
	on	cmo.CoupleID	= c.ID

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-27 : 20:15:09

This is on the assumption that all Members is part of a Couple (record in Members will have a record in CoupleMembers). If not, use LEFT JOIN
Members LEFT JOIN CoupleMembers

----------------------------------
'KH'

MrRay
Starting Member

28 Posts

Posted - 2006-02-27 : 22:18:03

Wonderful! Thanks for opening my eyes. This has been very helpful. And yes, not all members belong to a couple (unmarried children, etc.), so LEFT JOIN will be necessary.

Thanks again!

quote:
Originally posted by khtan

This is on the assumption that all Members is part of a Couple (record in Members will have a record in CoupleMembers). If not, use LEFT JOIN
Members LEFT JOIN CoupleMembers

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources