Query to display multiple records on one row

[cantona]

I have the following information held in 3 tables. The tables hold information about parents applying for school places for their children

Table 1 - Holds basic parent info..

Person_ID
Surname
Forename

Table 2 - Holds info on what choices the parents have made..
Person_ID
Preference_no
School_ID

Table 3 - Identifies the School Name chosen
School_ID
School_name

Im trying to produce a query that will list just one row for each person displaying their name, preference_no (a parent can state up to 3 preferences)and the school name. The query would contain the following;

Surname, forename, preference1, School_name, Preference2, school_name, preference3, school_name

How would i go about this? at the moment i get the following;
Name Preference School
John Smith 1 Test School
John Smith 2 Another Test School
John Smith 3 Further test school

I cant think how to get all on one line??

 

[lespaul]

Check out FAQ701-4233

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[cantona]

Thanks for that..

Not very familier with Functions though.. Is there any way i could achieve using sub queries? or multiple instances of tables?

 

[lespaul]

If you mean that you want a comma separated list of your fields, then, no I don't think you can do that in a query.

If what you mean is you want a single record returned listing all the preferences, that depends. Is there a maximum of 3 preferences? Or do some people have 6 preferences?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[cantona]

I would like a column called say Preference1 which would contain the number1, next to it would be a column called School_name1 with the school that is preference 1. The same would be needed for preference2 and preference3. There would only ever be 3 preferences

Thanks

 

[PHV]

Something like this (SQL code) ?
SELECT A.Surname, A.Forename
, P1.Preference_no AS Preference1, P1.School_name AS School1
, P2.Preference_no AS Preference2, P2.School_name AS School2
, P3.Preference_no AS Preference3, P3.School_name AS School3
FROM ((Table1 AS A
LEFT JOIN (
SELECT X.Person_ID, X.Preference_no, Y.School_name
FROM Table2 AS X INNER JOIN Table3 AS Y ON X.School_ID = Y.School_ID
WHERE X.Preference_no = 1
) AS P1 ON A.Person_ID = P1.PersonID)
LEFT JOIN (
SELECT X.Person_ID, X.Preference_no, Y.School_name
FROM Table2 AS X INNER JOIN Table3 AS Y ON X.School_ID = Y.School_ID
WHERE X.Preference_no = 2
) AS P2 ON A.Person_ID = P2.PersonID)
LEFT JOIN (
SELECT X.Person_ID, X.Preference_no, Y.School_name
FROM Table2 AS X INNER JOIN Table3 AS Y ON X.School_ID = Y.School_ID
WHERE X.Preference_no = 3
) AS P3 ON A.Person_ID = P3.PersonID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886