CODEWITHSUNDEEP
mysqljoinleft-join
Chris
Chris

Reputation: 2735

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, but 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.

Upvotes: 1

Views: 1850

Answers (3)

TelegramSam
TelegramSam

Reputation: 2790

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453287

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;

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 838216

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

Upvotes: 1

Related Questions