Re: combine multiple rows from joined tables into single row
Posted by:
Joe Gaber
Date: October 15, 2008 11:12PM
Here's my attempt at your suggestion:
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(';','<address type="', ad.address_type_id, '">',
'<address_1>', ad.address_1, '</address_1>'
)
SEPARATOR ";"
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';
however, I get an error with the syntax starting at the FROM