combine multiple rows from joined tables into single row

[Joe Gaber]

Here is a query that gathers data from various tables. Many of the tables are linked with the <table>_to_<table> name to account for many-to-many relations.

SELECT p.*, cd.*, cdt.type_name AS datatypename, ad.*, adt.type_name AS addtypename, a.*, co.company_name, at.type_name AS accttypename, my.*
FROM account AS a
LEFT JOIN account_type at USING (account_type_id)
LEFT JOIN person as p USING(person_id)
LEFT JOIN person_to_company p2co USING (person_id)
LEFT JOIN company co ON p.person_id = p2co.person_id AND co.company_id = p2co.company_id
LEFT JOIN person_to_address p2ad USING (person_id)
LEFT JOIN address ad ON p.person_id = p2ad.person_id AND ad.address_id = p2ad.address_id
LEFT JOIN address_type adt USING (address_type_id)
LEFT JOIN person_to_contact_data p2cd USING (person_id)
LEFT JOIN contact_data cd ON p.person_id = p2cd.person_id AND cd.contact_data_id = p2cd.contact_data_id
LEFT JOIN contact_data_type cdt USING (contact_data_type_id)
LEFT JOIN my_settings my USING(my_settings_id)
WHERE a.username = 'Joe'
AND a.password = md5('Gaber');

The result set will repeat the columns for some of the tables. For example, if a person has 1 account but 6 different contact_data records (email, ph, etc) and 3 different addresses (home, office, etc), there will be a total of 18 records in the result set. This requires complex programming code to properly loop through and know which address or which contact_data record is needed in a form or other display.

What I would like is to put all of the data into one long row as if the person table had all the address and all of the contact_data fields necessary to store the above mentioned number of records into a single record - in otherwords, a denormalized design versus the highly normalized one that I have now.

I know how to do this with one table using the following query:

SELECT distinct(common field),
(SELECT repeat column) AS colb,
(SELECT repeat column) AS colc
FROM table

I tried the following test query:

SELECT DISTINCT
p.person_id,
(SELECT address_1 FROM address AS ad1 LEFT JOIN person_to_address AS p2a1 ON ad1.address_id = p2a1.address_id WHERE p2a1.person_id = 22) AS HOME,
(SELECT address_1 FROM address AS ad2 LEFT JOIN person_to_address AS p2a2 ON ad2.address_id = p2a2.address_id WHERE p2a2.person_id = 22) AS OFFICE
FROM person AS p;

However, after trying for a couple hours to try and incorporate the JOINS, I kept getting "Subquery returns more than 1 row".

Can someone help me construct the query that produces a single row with all 6 address records individually aliased along with all 3 contact_data records aliased. This would produce a row that I want to look like the following condensed record.

----------------------------------------------------------------------------------
|fname | lname | homePh | offPh | homeAdd | homeCity | offAdd | offCity | etc... |
----------------------------------------------------------------------------------

Thanks in advance.