MySQL Query "One to Many" question.

Question

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.

My query looks like this:

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

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.)

stdClass Object
(
    [full_name] => John Mueller
    [title] => President
    [company] => Mueller Co.
    [background] => This is the contacts background info.
    [email_type] => 1,1,1,1
    [email] => [email protected],[email protected],[email protected],[email protected]
    [phone_type] => 1,2,3,4
    [phone] => (123) 555-1212,(123) 555-1213,(123) 555-1214,(123) 555-1215
    [website_type] => 1,1,1,1
    [website] => www.mc.com,www.mc.com,www.mc.com,www.mc.com
    [address_type] => 1,1,1,1
    [street] => {address_1},{address_1},{address_1},{address_1}
    [city] => {city_1},{city_1},{city_1},{city_1}
    [state] => {state_1},{state_1},{state_1},{state_1}
    [zip] => {zip_1},{zip_1},{zip_1},{zip_1}
    [country] => 
)

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.

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.

Thanks again for any help anyone can offer!

UPDATE:

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.

Patial new query solution:

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

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.

Answer 1

Wouldn't it be easier to get the 'base' information to the application first (the fields that do not repeat) and then use separate queries for the repeating information using sub-forms within the main form?

Or, as a compromise, create a view that displays up to N number of repeating fields.

Answer 2

Throw DISTINCT in there.

GROUP_CONCAT(DISTINCT email.email_type ORDER BY email.email_type)as email_type,

Ref

Answer 3

The problem is, as soon as you join on PHONE (in your example), the query returns four records. Using GROUP_CONCAT in the top-level SELECT list isn't going to fix that. First solution that comes to mind is to use inner queries to guarantee you'll only return one record for a given contact.

SELECT 
contacts.full_name,
[...]
inner_phone.phones
FROM contacts 
LEFT JOIN (SELECT GROUP_CONCAT(phone ORDER BY phone_type) AS phones, contact_id FROM phone) inner_phone ON contacts.ID = inner_phone.contact_id
[... etc. for other tables ...]

That should put you on the right track, unless MySQL has vastly different subquery behavior from MSSQL/Oracle...