How do I collapse multiple rows into on row

[rljesse]

I have an prebuilt application that I need to create a query for that combines two records in one table to create a single record that has both husband and wife in it.


Person Table

+-------+--------+-----------+---------------+
| FamID | PersID | FirstName | Role |
+-------+--------+-----------+---------------+
| 1 | 1 | Rick | Head of House |
| 1 | 2 | Rachel | Spouse |
| 2 | 3 | John | Head of House |
| 3 | 4 | Peter | Head of House |
| 3 | 5 | Betty | Spouse |
| 3 | 6 | Johnny | Child |
+-------+--------+-----------+---------------+

I want to combine the records such that I create the following table;

Result Table I would Like;
+-----------+---------------+-----------+--------+
| firstname | role | firstname | role |
+-----------+---------------+-----------+--------+
| Rick | Head of House | Rachel | Spouse |
| John | Head of House | | |
| Peter | Head of House | Betty | Spouse |
+-----------+---------------+-----------+--------+

However using the following query I can only get records where both a "Head of House" and "Spouse" exists.


"select person.firstname, person.role, person2.firstname, person2.role from
person, person person2 where ( person.role = "Head of House") and
(person2.role = "Spouse") and ( person.persID <> person2.persID ) and (
person.FamID = person2.FamID ) ;"


Result of Above Query
+-----------+---------------+-----------+--------+
| firstname | role | firstname | role |
+-----------+---------------+-----------+--------+
| Rick | Head of House | Rachel | Spouse |
| Peter | Head of House | Betty | Spouse |
+-----------+---------------+-----------+--------+

 

[PHV]

Use an OUTERr join:
SELECT H.firstname, H.role, S.firstname, S.role
FROM person H LEFT JOIN person S ON H.FamID = S.FamID AND H.persID <> S.persID AND S.role = 'Spouse'
WHERE H.role = 'Head of House'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886