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