![]() However, after trying for a couple hours to try and incorporate the JOINS, I kept getting "Subquery returns more than 1 row".Ĭan 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. (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 (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, I know how to do this with one table using the following query: ![]() 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. 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. 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. The result set will repeat the columns for some of the tables. LEFT JOIN my_settings my USING(my_settings_id) LEFT JOIN contact_data_type cdt USING (contact_data_type_id) LEFT JOIN contact_data cd ON p.person_id = p2cd.person_id AND cd.contact_data_id = ntact_data_id LEFT JOIN person_to_contact_data p2cd USING (person_id) LEFT JOIN address_type adt USING (address_type_id) LEFT JOIN address ad ON p.person_id = p2ad.person_id AND ad.address_id = p2ad.address_id LEFT JOIN person_to_address p2ad USING (person_id) LEFT JOIN company co ON p.person_id = p2co.person_id AND co.company_id = pany_id LEFT JOIN person_to_company p2co USING (person_id) LEFT JOIN account_type at USING (account_type_id) SELECT p.*, cd.*, cdt.type_name AS datatypename, ad.*, adt.type_name AS addtypename, a.*, co.company_name, at.type_name AS accttypename, my.* Many of the tables are linked with the _to_ name to account for many-to-many relations. Here is a query that gathers data from various tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |