Databases
StackOverflow2013
Postsdbo
POMySQL Query "One to Many" question
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Query "One to Many" question
    primarykey
    data
    text
    <p>OK I am going to try to explain this the best I can and maybe someone will understand it. I have a CRM application I am building and have the following tables: contacts, email, phone, website and address. I have been trying to create a Query that gathers all the info into one result set. I have kind of found a way that works 99.9% but I think I am missing something for that 1% to work and have killed myself trying to find it. </p> <p>My query looks like this: </p> <pre><code>SELECT contacts.full_name, contacts.title, contacts.company, contacts.background, GROUP_CONCAT( email.email_type ORDER BY email.email_type)as email_type, GROUP_CONCAT( email.email ORDER BY email.email_type)as email, GROUP_CONCAT( phone.phone_type ORDER BY phone.phone_type)as phone_type, GROUP_CONCAT( phone.phone ORDER BY phone.phone_type)as phone, GROUP_CONCAT( website.website_type ORDER BY website.website_type)as website_type, GROUP_CONCAT( website.website ORDER BY website.website_type)as website, GROUP_CONCAT( address.type ORDER BY address.type ) as address_type, GROUP_CONCAT( address.address_street ORDER BY address.type ) as street, GROUP_CONCAT( address.address_city ORDER BY address.type ) as city, GROUP_CONCAT( address.address_state ORDER BY address.type ) as state, GROUP_CONCAT( address.address_zip ORDER BY address.type ) as zip, GROUP_CONCAT( address.address_country ORDER BY address.type) as country FROM contacts Left Join email ON contacts.id = email.contact_id Left Join phone ON contacts.id = phone.contact_id Left Join website ON contacts.id = website.contact_id Left Join address ON contacts.id = address.contact_id GROUP BY contacts.id ORDER BY contacts.id ASC </code></pre> <p>Now like i said it works like 99.9% of the way I want it to but here is the result set: (now this is a simulated result object but it follows what currently is spit out after the query.)</p> <pre><code>stdClass Object ( [full_name] =&gt; John Mueller [title] =&gt; President [company] =&gt; Mueller Co. [background] =&gt; This is the contacts background info. [email_type] =&gt; 1,1,1,1 [email] =&gt; jm@mc.com,jm@mc.com,jm@mc.com,jm@mc.com [phone_type] =&gt; 1,2,3,4 [phone] =&gt; (123) 555-1212,(123) 555-1213,(123) 555-1214,(123) 555-1215 [website_type] =&gt; 1,1,1,1 [website] =&gt; www.mc.com,www.mc.com,www.mc.com,www.mc.com [address_type] =&gt; 1,1,1,1 [street] =&gt; {address_1},{address_1},{address_1},{address_1} [city] =&gt; {city_1},{city_1},{city_1},{city_1} [state] =&gt; {state_1},{state_1},{state_1},{state_1} [zip] =&gt; {zip_1},{zip_1},{zip_1},{zip_1} [country] =&gt; ) </code></pre> <p>Now as you can see the result acts like I want it to except for when on one the items has multiple valid items, i.e. in this case John has 4 types of phone numbers and this causes the other records in the DB to multiply accordingly. So in this case you get 4 of the same item in all the other supporting tables. </p> <p>I have tried everything I can and maybe it can't be done but I thought I would try one more time and see if anyone would look at it and say oh yea you're missing 5 letters that will make it work or something. At this point even a "your stupid that won't work" woud be great too. </p> <p>Thanks again for any help anyone can offer!</p> <p>UPDATE:</p> <p>I feel Like such a noob now, I pulled a classic mistake: i checked my result without full verification, but at the same time by the information i provided it was not clear. I'll explain my original solution worked except that when i had 3 work (type=1) numbers i would end up with a result like phone_type => 1 and phone => 555-1212,555-1213,555-1214 this is fine but when i have 2 work and 1 home the type identifiers were useless, but i never said that you can have more than one of any type for a contact so actually both of my answer below are correct, with that said the sql was a bit malformed but i knew what he was saying so it actually worked better and correct on the nose. BTW just pulling distint from the type fields wouldn't do it either ... I tried that. </p> <p>Patial new query solution: </p> <pre><code>SELECT contacts.full_name, contacts.title, contacts.company, contacts.background, inner_phone.phone, inner_phone_type.phone_type FROM contacts left Join (SELECT phone.contact_id, GROUP_CONCAT(phone.phone ORDER BY phone.phone_type) as phone FROM phone GROUP BY phone.contact_id ) inner_phone ON contacts.id = inner_phone.contact_id left Join (SELECT phone.contact_id, GROUP_CONCAT(phone.phone_type ORDER BY phone.phone_type) as phone_type FROM phone GROUP BY phone.contact_id ) inner_phone_type ON contacts.id = inner_phone_type.contact_id ORDER BY contacts.id ASC </code></pre> <p>Thank you for your answers, and as a side note I just canceled that overpriced Experts Exchange this is so much easier to use and find what you are looking for and best of all free ;) - thanks again. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBrandonS
    1. USBrandonS
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL Query "One to Many" question
      1. This table or related slice is empty.
      1. VTUpMod
    1. COoh yea and the placement of the group by was critical just in case someone has a similar problem.
      singulars
      1. POMySQL Query "One to Many" question
      1. USBrandonS
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload