MySQL - Select last record from second table matching with first table

Question

I have two tables customers and orders, below is the structure.

Table - customers

• id
• customer_name

Table - orders

• id
• order_id
• customer_id

customers table have customers records and orders table have orders placed by customers,

customer_id in orders table is linked to the id field of customers table.

Now one customer can have zero or one or more than one orders, i want to get the last order placed by customers only.

when i run the following query a simple invisible join, it returns all the orders by the customer

SELECT customers.customer_name,orders.order_id FROM orders,customers WHERE orders.customer_id=customers.id

I have also tried different JOIN statements but cannot get the last order by the customer, i want to get it in one SQL query for all customers.

Thank you in advance for your help.

Answer 1

In MySQL there is just few ways to make it work (that I now actually). The first one is sort your table as desc before the join:

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN orders o 
    ON o.id = (SELECT id FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 1)

Using in real time is the only way to get it done, but if you need to make some join on not real time you can create a temporary table or a alias table sorting it to make your select, like this:

CREATE TABLE tmp_your_table AS 
SELECT * FROM orders ORDER BY id DESC

So now you are able to make this join work:

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN tmp_your_table o ON o.id = tmp_your_table.id

Answer 2

Try this query

SELECT 
   c.customer_name, 
   max(o.order_id)
FROM 
   customers c
INNER JOIN
   orders o
ON
   o.customer_id = c.id
GROUP BY 
   c.customer_name

You don't have any date field in the order table so assuming the latest order will be the one which has max(order_id).