Complex query

[Roger Whitehead]

(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)

[Razvan Socol]

Hello, Roger

See if this helps:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-
column-into-a-single-row.html

--
Razvan Socol
SQL Server MVP

[Dave Ballantyne]

Hi Roger,

What Error are you getting , the only real fault i can spy from a quick
eyeball is
"AND people.peo_personID"

needs to be

"AND people.peo_personID = <something>"

Also the design isnt to great , what if there are not 4 people in a
family ? If there are less ,for the query to work, you need redundant
rows in the people_X tables, any more and you cant support them.

Dave

[Roger Whitehead]

This is the hard-coded way (below), which gives the kind of result I need,
but I can't do this for the whole table. Also, some families have varying
number of members.

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 testbyroger.dbo.family family

, testbyroger.dbo.People People

, testbyroger.dbo.People People_1

, testbyroger.dbo.People People_2

, testbyroger.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

-- I know the peo_personid values so

-- not a long term solution-

AND ((People.peo_personid <> 0)

AND (People_1.peo_personid=3)

AND (People_2.peo_personid=4)

AND (People_3.peo_personid=5)

)

Thanks for your time.
roger

"Dave Ballantyne" <no_spam_8...@yahoo.com> wrote in message
news:OdKgwEbB...@TK2MSFTNGP03.phx.gbl...

[Roger Whitehead]

Sorry Dave, missed your final para first time around.

I agree, that's (one reason) why my query is so pants! I imagine that I
could gather the relevant rows from the people table using a GROUP BY. As
this kind of relationship is not unique, I was hoping someone might have
done this before me.

Regards
Roger

"Dave Ballantyne" <no_spam_8...@yahoo.com> wrote in message
news:OdKgwEbB...@TK2MSFTNGP03.phx.gbl...

[Roger Whitehead]

Dave those are both brilliant - thanks for your time. I was suspecting that
I might have to dump the results into Excel and VBA them into shape (what a
pity :-)), but had hoped for a slicker method. I'll no doubt tinker further
with your more complex solution in idle moments.

Thanks again
Roger

"Dave Ballantyne" <no_spam_8...@yahoo.com> wrote in message

news:uiYPKsbB...@TK2MSFTNGP03.phx.gbl...
> This is the nearest i can get to what you want....
>
>
> drop table People
> drop table family
> go
> drop table inp
>
> go
>
> Create table family
> (
> FamilyId integer)
> go
> Create table People
> (
> Personid integer,
> FamilyId integer,
> Forename varchar(50),
> LastName varchar(50)
> )
>
> go
> delete from family
>
> go
> delete from people
> go
> insert into family values(1)
> insert into people values(1,1,'John','Smith')
> insert into people values(2,1,'Alice','Smith')
> insert into family values(2)
> insert into people values(3,2,'John','Jones')
> insert into people values(4,2,'Alice','Jones')
> insert into people values(5,2,'Carl','Jones')
>
> select familyid,max([1]),max([2]),max([3]),max([4]),max([5])
> From (SELECT family.FamilyId,
> People.forename,
> People.lastname,
> FullName = Forename+' '+lastname,
> rownumber=row_number() OVER (PARTITION BY family.familyid order by
> family.familyid,People.personid )
> FROM family,
> People
> WHERE family.familyid in(1,2)
> and family.familyid = People.familyid) as inp
> pivot(
> max(fullname)
> for rownumber in([1],[2],[3],[4],[5])
> ) as pvt
> group by familyid
>
>
> Which is fairly interesting as an academic excercise :).
>
> I would urge you to use a much simpler
>
> SELECT family.FamilyId,
> People.Personid, People.forename,
> People.lastname
> from family,
> People
> WHERE family.familyid in(1,2)
> and family.familyid = People.familyid
>
> and do any "pivoting" as a front end exercise though.....
>
>
> Dave

[Dave Ballantyne]

go

go
delete from family

Dave

[Dave Ballantyne]

Not really a pity , just the right tool for the right job.
Its not a pity that you cant hammer in nails with a screwdriver as well
as you can with a hammer, it'll do it eventually, but its the wrong
tool. :)

Glad to be of help anyway