MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.