MySQL Forums :: Newbie :: combine multiple rows from joined tables into single row


Advanced Search

combine multiple rows from joined tables into single row
Posted by: Joe Gaber ()
Date: September 30, 2008 10:13AM

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.

Options: ReplyQuote


Subject Written By Posted
combine multiple rows from joined tables into single row Joe Gaber 09/30/2008 10:13AM
Re: combine multiple rows from joined tables into single row David Payne 09/30/2008 10:27AM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/15/2008 10:54PM
Re: combine multiple rows from joined tables into single row Huu Da Tran 10/01/2008 08:41AM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/15/2008 11:00PM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/15/2008 11:12PM
Re: combine multiple rows from joined tables into single row Huu Da Tran 10/16/2008 08:43AM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/19/2008 09:06PM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/19/2008 09:23PM
Re: combine multiple rows from joined tables into single row Joe Gaber 10/19/2008 09:26PM


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.