Re: combine multiple rows from joined tables into single row

[Joe Gaber]

Oh... and missing GROUP BY command. Here's the basic query that works. I will build on the CONCAT part now that I understand the basic strucuture.

SELECT p.*, a.*, co.company_name, ad.*, at.type_name AS accttypename, adt.type_name AS addtypename, cd.*, cdt.type_name AS datatypename, my.*,
GROUP_CONCAT(CONCAT(';', ad.address_type_id, ad.address_1))

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 username = 'Joe'
GROUP BY account_id;