MySQL Subquery related to LEFT JOINS -

i given task of extracting customer information database , stuck on last part! hope explanation sufficient describe problem , attempts.

goal: return 1 row per customer phone number

the problem: each customer may have many phone numbers

attempts:

subquery: getting "subquery returning more 1 row" error. makes sense me cannot insert statement per customer

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, (select telephone.number      customer     left join customertelephone on customer.customerid = customertelephone.customerid     left join telephone on customertelephone.telephoneid = telephone.telephoneid      telephone.type = "main") mainphone `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: query returns 1 row/customer/number, need each number in 1 row.

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, telephone.number `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: query returns 1 row per customer, returns first number.

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, telephone.number `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 customer.customerid limit 100 

how can return 1 row per customer display each of phone number in 1 row?

---

edit:

i received awesome help: group_concat wonders! trying format query return properly.

goal: separate values returned group_concat 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) telephonenumbers, group_concat(telephone.type) types `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 customer.customerid 

current result of group_conact:

telephonenumbers                 type 321-000-0000,321-000-0000      main, fax 321-001-0000                   mobile 

what trying achieve:

    main           fax            mobile 321-000-0000   321-000-0000        null     null           null        321-001-0000 

attempts: statement in group_concat, throws error

group_concat(telephone.number group_concat(telephone.type) = "main") main 

is possible achieve this?

---

edit:

final code (thank user4829935!):

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, group_concat(tmain.number) main, group_concat(tmobile.number) mobile, group_concat(tfax.number) fax `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 tmain on customertelephone.telephoneid = tmain.telephoneid , tmain.type = 'main' left join telephone tmobile on customertelephone.telephoneid = tmobile.telephoneid , tmobile.type = 'mobile' left join telephone tfax on customertelephone.telephoneid = tfax.telephoneid , tfax.type = 'fax' group customer.customerid 

try this:

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, group_concat(telephone.number) `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 customer.customerid 

you phone numbers separated comma.

edit:

to different numbers 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 know possible types of phone numbers ahead of time, , code them query. want?

that like:

select customer.name, address.street, address.city, address.state, address.zip, customer.contact, email.address, customer.ctype, group_concat(tm.number) maintelephone, group_concat(tf.number) fax `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 tm on customertelephone.telephoneid = tm.telephoneid , tm.type = 'maintelephone' left join telephone tf on customertelephone.telephoneid = tf.telephoneid , tf.type = 'fax' group customer.customerid 

(i'm typing out of head, don't have data verify queries. there might typos)