CODEWITHSUNDEEP
mysql
Wige
Wige

Reputation: 3918

MySQL - Joining two tables without duplicates?

I have two tables that I am trying to join. One contains a list of customers, the other is a list of orders. I am trying to formulate a query that will allow me to select all of the customers listed in the table customers who have at least one order in the table orders. However, I do not want to get duplicates for those customers who have multiple orders. Any suggestions how I can accomplish this?

I know this is probably a common issue, however I have no idea what this type of query would be called so that I could search for an answer. Any suggestions would be greatly appreciated. Thanks.

Upvotes: 9

Views: 51321

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

SELECT c.*
  FROM CUSTOMERS c
 WHERE EXISTS (SELECT NULL
                 FROM ORDERS o
                WHERE o.custeromid = c.id)

The IN clause is an alternative, but EXISTS works better for duplicates because it returns true on the first duplicate so it doesn't process the entire table.

Upvotes: 7

orangepips
orangepips

Reputation: 9971

SELECT
  c.id, 
  c.name
FROM
  customer c
    INNER JOIN order o ON o.customer_id = c.id
GROUP BY 
  c.id,
  c.name
HAVING
  COUNT(o.id) >= 1

Can't remember if HAVING or GROUP BY comes first.

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

select customers.id, customers.name, count(orders.id)
from customers 
   inner join orders on orders.customer_id = customers.Id
group by customers.id, customers.name
having count(orders.id) > 0

Upvotes: 2

Michael Kopinsky
Michael Kopinsky

Reputation: 904

It's much simpler than you may think:

select distinct(customer_id) from orders;

Edit: If you actually want to get the full info on the customer,

select * from customers where customer_id in (select distinct(customer_id) from orders);

Upvotes: 11

Related Questions