mysqljoinleft-joingroupwise-maximum

MySQL Join Without Duplicates


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


Solution

  • 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