Search code examples
mysqlsubquerycorrelated-subquery

MySQL Subquery related to LEFT JOINS

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
Solution

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