MySQL - joining two tables without duplicates?

Question

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

I know this is probably a common problem, but I have no idea how this type of request will be called so that I can find the answer. Any suggestions would be appreciated. Thanks.

Answer 1

This is much simpler than you think:

select distinct(customer_id) from orders;

Edit: if you really want to get complete customer information,

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

Answer 2

Using:

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

The IN parameter is an alternative, but EXISTS works better for duplicates, because it returns true in the first duplicate, so it does not process the entire table.

Answer 3

 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 

Answer 4

 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 

It is impossible to remember whether HAVING or GROUP BY comes first.

Answer 5

SELECT C.customer_id, firstorder.order_id FROM CLIENTS as C LEFT SELECT customer_id, min (order_id) FROM orders GROUP BY customer_id) AS firstorder ON c.customer_id = firstorder.customer_id