Complex Join -- sql server new users

Roger Whitehead
10/3/2007 12:00:00 AM

(Well, it's complex to me...)

I have two tables;
1) People - which has a many-to-one relationship with
2) Family

[People] has a PK[peo_personid] and a Foreign Key [peo_familyid] from the
[family] table

I'm trying to get a query output of one record per family, roughly as
follows

--Begins
SELECT People.peo_personid

, People.peo_forename AS 'Person1 Forename'

, People.peo_lastname AS 'Person1 Surname'

, People_1.peo_forename As 'Person2 Forename'

, People_1.peo_lastname AS 'Person2 Surname'

, People_2.peo_forename AS 'Person3 Forename'

, People_2.peo_lastname AS 'Person3 Surname'

, People_3.peo_forename AS 'Person4 Forename'

, People_3.peo_lastname AS 'Person4 Surname'

FROM db3.dbo.family family

, db3.dbo.People People

, db3.dbo.People People_1

, db3.dbo.People People_2

, db3.dbo.People People_3

WHERE People.peo_familyid = family.familyid

AND family.familyid = People_1.peo_familyid

AND family.familyid = People_2.peo_familyid

AND family.familyid = People_3.peo_familyid

AND people.peo_personID

-- all sorts of failures in the WHERE clause follow...

--Ends

THE OTHER EMBARRASSING BIT: I'm using SQLServer Management Studio Express...

Any pointers welcome.

Thanks

Roger

Shaftesbury (UK)

Russell Fields
10/5/2007 10:28:47 AM

Roger,

Apparently you are trying to get a limited set of names from a family onto a
single row. Something like:

123, George, Jones, Sally, Jones, Jane, Jones, David Jones

Since you are using 2005 (don't be embarrassed about Express) you can use
the PIVOT operator, which is documented in the SQL Server Books Online help
documents. Some sample code can be found here:
http://www.sqlmag.com/Articles/ArticleID/42901/42901.html

Of course, if the Jones family has more than 4 people you need to decide
what to do about persons 5 and on.

RLF

[quoted text, click to view]

sql server new users : Complex Join