MySQL Join Without Duplicates

Question

I have a customers table, and an orders table. Each customer can have many orders.

I want to select every customer, along with their earliest order number from the orders table (it is important that I select the earliest order number, not just any order). I want to list customers whether they have an order or not, and I don't want to include customers twice if they have more than one order.

I'm using this:

  SELECT *
    FROM customers
    LEFT JOIN orders
    ON customers.id = orders.customer_id 
    GROUP BY customers.id

This gives me almost what I want, except it will pick whatever order ID it likes from the table. I need to be able to sort, and pick the smallest order ID.

Any ideas please?

Im pretty sure its something that's staring me in the face...

EDIT: Tables' Structures as requested

Customers:

| ID | Name | Address            | Etc |
----------------------------------------
| 1  | Joe  | 123 Fake Street    |     |
| 2  | Mike | 1600 Fake Road     |     |
| 3  | Bill | Red Square, Moscow |     |
----------------------------------------

Orders:

| ID | Customer_ID | Date |
---------------------------
| 1  | 1           |  ... |
| 2  | 2           |  ... |
| 3  | 2           |  ... |
| 4  | 1           |  ... |
---------------------------

Can you post the tables structure? – Nico Jun 13 '12 at 12:20 — Nico, Jun 13 '12 at 12:20

score 7 · Accepted Answer · answered Jun 13 '12 at 12:23

7

Create a virtual table (a/k/a subquery) with the lowest numerical order ID for each customer.

SELECT customer_id, min(order_id)
  FROM orders
 GROUP BY customer_id

Then join that table with the customer table, like so.

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

answered Jun 13 '12 at 12:23

O. Jones

103,626
17
118
172

Thats done it! Great, thanks a lot. I knew the answer would be somethign fairly simple :) – Tyler Durden Jun 13 '12 at 12:35
2

Also Consider to have a look at: HAVING MIN(order_id) = order_id – velop Jan 14 '15 at 09:12

score 1 · Answer 2 · answered Jun 13 '12 at 12:22

1

Try this

Select customer.*,Order.OrderNo As EarilerORderNo
From Customers Left Join 
(Select customer_id,orderid from orders order by customer_id,orderid desc) As Orders 
ON Customers.Id=Orders.OrderID

answered Jun 13 '12 at 12:22

Sudhakar B

1,465
9
16

MySQL Join Without Duplicates

2 Answers2