One query join with multiple rows from one table

Ok, so I have two tables in MySQL. One table holds customer information, the other holds phone numbers. I need to join these tables in one query to select up to two phone numbers from the phones table as well as customer information. right now my query is:

SELECT customers.name, phones.phone, phones2.phone 
FROM customers 
LEFT JOIN phones ON phones.customerid=customers.id 
LEFT JOIN phones AS phones2 ON phones2.customerid=customers.id 
GROUP BY customers.id;

However, this returns the same phone number for phone 1 and phone 2. essentially what I need to do is offset phones2 by 1, 开发者_StackOverflowbut I don't know how to do that syntactically.

The phones are in a separate table because it's a one to many relationship.

I need this to be in one query because I'm exporting this directly to a csv.

Help is much appreciated. Thanks in advance.

You just want an arbitrary 0,1, or 2 phone numbers per customer then? If so Max and Min OK? This will save you a join.

SELECT customers.name, 
       MAX(phones.phone) AS phone, 
       CASE WHEN MAX(phones.phone) = MIN(phones.phone) THEN NULL ELSE MIN(phones.phone) END AS phone2
FROM customers 
LEFT JOIN phones ON phones.customerid=customers.id 
GROUP BY customers.id;

To avoid getting the same phone number twice you could change this:

LEFT JOIN phones AS phones2 ON phones2.customerid=customers.id 

To this:

LEFT JOIN phones AS phones2 ON phones2.customerid=customers.id
AND phones2.phone <> phones.phone

What you want is to do remove the cases where the numbers are the same.

SELECT customers.name, phones.phone, phones2.phone 
FROM customers 
LEFT JOIN phones ON phones.customerid=customers.id 
LEFT JOIN phones AS phones2 ON phones2.customerid=customers.id 
WHERE phones2.phone != phones.phone
GROUP BY customers.id