MySQL Subquery related to LEFT JOINS

Question

I was given the task of extracting customer information from a database and I am stuck on the last part! I hope my explanation is sufficient to describe my problem and attempts.

Goal: Return one row per customer with their all phone number

The Problem: Each customer may have many phone numbers

Related Diagram:

Attempts:

SUBQUERY: Getting a "Subquery returning more than 1 row" error. This makes sense to me but I cannot insert a WHERE statement per customer

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE,
(SELECT telephone.Number 
    FROM customer
    LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
    LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID 
    WHERE telephone.Type = "Main") as MainPhone
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LIMIT 100

LEFT JOIN: The query returns one row/customer/number, but I need each number in one row.

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, telephone.Number
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID 
LIMIT 100

GROUP BY: The query properly returns one row per customer, but only returns the first number.

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, telephone.Number
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID
LIMIT 100

How can I return one row per customer display each of their phone number in only one row?

Edit:

I just received some awesome help: group_concat does wonders! Now I am trying to format the query return properly.

Goal: Separate the values returned by GROUP_CONCAT into new fields

Current SQL Code:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(telephone.Number) as TelephoneNumbers, GROUP_CONCAT(telephone.Type) as Types
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID

Current result of GROUP_CONACT:

TelephoneNumbers                 Type
321-000-0000,321-000-0000      Main, Fax
321-001-0000                   Mobile

What I am trying to achieve:

    Main           Fax            Mobile
321-000-0000   321-000-0000        NULL
    NULL           NULL        321-001-0000

Attempts: WHERE statement in GROUP_CONCAT, which throws an error

GROUP_CONCAT(telephone.Number WHERE GROUP_CONCAT(telephone.Type) = "MAIN") as Main

Is it possible to even achieve this?

EDIT:

Final code (thank you user4829935!):

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(tmain.Number) as Main, GROUP_CONCAT(tmobile.Number) as Mobile, GROUP_CONCAT(tfax.Number) as Fax
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone as tmain ON customertelephone.TelephoneID = tmain.TelephoneID AND tmain.type = 'Main'
LEFT JOIN telephone as tmobile ON customertelephone.TelephoneID = tmobile.TelephoneID AND tmobile.type = 'Mobile'
LEFT JOIN telephone as tfax ON customertelephone.TelephoneID = tfax.TelephoneID AND tfax.type = 'Fax'
GROUP BY customer.CustomerID

Hello Abhik, thank you for your response--could you clarify: would I place the group by at the end of the query and the group_concat in the select statement? Thanks — Nathan_Sharktek, Apr 29 '15 at 16:16
`select ....,group_concat(telephone.Number) as Number` this would return all the `Number` as comma separation from the `n-m` relation here m is the `telephone` — Abhik Chakraborty, Apr 29 '15 at 16:18
Why do you have more than two joins !?! If the goal is "Return one row per customer with their all phone number [sic]" then two joins will suffice. Typically, issues of data display (if that's the issue) are best handled in the presentation layer/application-level code (e.g. a simple PHP loop) — Strawberry, Apr 29 '15 at 17:00

Ada Lovelace · Accepted Answer · 2015-04-29T16:54:05.303

Try this:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(telephone.Number)
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone ON customertelephone.TelephoneID = telephone.TelephoneID
GROUP BY customer.CustomerID

You will get phone numbers separated by comma.

EDIT:

To get different numbers as different fields, such as:

name      street      city        state  zip    ... main_phone   fax
John Doe  123 Main St Springfield CA     99999      123-555-5555 123-555-5556

you need to know the possible types of phone numbers ahead of time, and code them into the query. Is that what you want?

That would be something like:

SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(tm.Number) as mainTelephone, GROUP_CONCAT(tf.Number) as fax
FROM `customer`
LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID
LEFT JOIN address ON customeraddress.AddressID = address.AddressID
LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID
LEFT JOIN email ON customeremail.EmailID = email.EMailID
LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID
LEFT JOIN telephone as tm ON customertelephone.TelephoneID = tm.TelephoneID AND tm.type = 'mainTelephone'
LEFT JOIN telephone as tf ON customertelephone.TelephoneID = tf.TelephoneID AND tf.type = 'fax'
GROUP BY customer.CustomerID

(I'm typing out of my head, as I don't have your data to verify my queries. There might be typos)

Exactly! I believe we are on the same wavelength. I edited my original question to better explain what I am attempting to do. thank you so much! And yes, the three types of possibly numbers are main, fax, and mobile — Nathan_Sharktek, Apr 29 '15 at 16:54
If you find my answer helpful, please up-vote it. I'm new here, trying to get reputation. — Ada Lovelace, Apr 29 '15 at 16:56
Does my latest query work? You can easily add additional phone types to it. — Ada Lovelace, Apr 29 '15 at 16:57
Thank you so much for your help! It did work--I will update my question to reflect the final sql code — Nathan_Sharktek, Apr 29 '15 at 17:00
A query of the second form has potential to generate "duplicate" telephone numbers, for example, if two rows are returned from `tm`, times three rows returned from `tf`. (This may not be an issue in this case, if there's a UNIQUE constraint on `TelephoneID` and `type`. In the more general case, we can include the `DISTINCT` keyword in the `GROUP_CONCAT` to eliminate generated duplicates. — spencer7593, Apr 29 '15 at 17:35

spencer7593 · Answer 2 · 2015-04-29T17:55:31.450

MySQL provides an aggregate function GROUP_CONCAT.

Reference: https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

(I'm not going to repeat the entirety of the documentation here.)

Some important notes: the length of the string returned by GROUP_CONCAT is limited by the setting of the max_group_concat_len variable, and also the max_allowed_packet variable.

If the string is longer, it's silently truncated to the maximum allowed length. You may want to check the length of the returned string and compare it to maximum allowed length to verify it's not been truncated.

The DISTINCT keyword is useful for eliminating duplicates.

The ORDER BY is useful for making the returned string more deterministic.

The default separator is a comma, but that can also be specified.

FOLLOWUP

To get distinct list of phone numbers by type, you can use a conditional test in a CASE expression or an IF function, to conditionally return a value or a NULL. Just wrap that expression in an aggregate function like GROUP_CONCAT, e.g.

SELECT GROUP_CONCAT( IF(t.type='main'  ,t.number,NULL) ) AS main_numbers
     , GROUP_CONCAT( IF(t.type='mobile',t.number,NULL) ) AS mobile_numbers
     , ...
  LEFT JOIN telephone t ON ...

If you only want one number per type, you could use the MAX aggregate function instead, e.g.

SELECT MAX( IF(t.type='main'  ,t.number,NULL) ) AS a_main_number
     , MAX( IF(t.type='mobile',t.number,NULL) ) AS a_mobile_number
     , ...
  LEFT JOIN telephone t ON ...

As another alternative (usually not as performant), you could achieve an equivalent result using a correlated subqueries in the SELECT list.

SELECT c.customerID
     , ( SELECT t.number
           FROM customertelephone ct
           JOIN telephone t
             ON t.telephoneID = ct.telephoneID
            AND t.type = 'main'                -- main
          WHERE ct.customerID = c.customerID   -- match column from outer query
          ORDER BY t.telephoneID
          LIMIT 0,1                            -- first row
       ) AS main_number_1
     , ( SELECT t.number
           FROM customertelephone ct
           JOIN telephone t
             ON t.telephoneID = ct.telephoneID
            AND t.type = 'main'                -- main
          WHERE ct.customerID = c.customerID   -- match column from outer query
          ORDER BY t.telephoneID
          LIMIT 1,1                            -- second row
       ) AS main_number_2
     , ( SELECT t.number
           FROM customertelephone ct
           JOIN telephone t
             ON t.telephoneID = ct.telephoneID
            AND t.type = 'mobile'               -- mobile
          WHERE ct.customerID = c.customerID    -- match column from outer query
          ORDER BY t.telephoneID
          LIMIT 0,1
       ) AS mobile_number_1

 FROM customer c

MySQL Subquery related to LEFT JOINS

2 Answers2