SUBSELECT (SQL)-CodePudding

I have to tables. The first table (customers) shows data about the customers (id, firstname, lastname). The second table shows data about the orders (timestamp and customer id). So, i wanted to see the last order by a customer in my table "customers" and did a SUBSELECT and it worked.

SELECT id,firstname, lastname,
 (SELECT timestamp FROM orders 
  WHERE customers.id = orders.customer_id 
  ORDER BY timestamp DESC LIMIT 1) AS last_order
FROM customers
WHERE (SELECT timestamp FROM orders 
  WHERE customers.id = orders.customer_id) IS NOT NULL

But, there are some customers who never ordered something and so is no value in the column "last_order". I am trying to filter these customers out with another SUBSELECT after the WHERE but i am failing. Can somebody help me?

CodePudding user response：

The problem is with the second sub-query, it may return more than one value (one customer may have multiple orders); it should return only one value, so you may limit it by 1 as the following:

WHERE (SELECT timestamp FROM orders
  WHERE customers.id = orders.customer_id Limit 1) IS NOT NULL

Another approach is to use Exists as the following:

SELECT id,firstname, lastname,
 (SELECT timestamp_ FROM orders
  WHERE customers.id = orders.customer_id
  ORDER BY timestamp_ DESC LIMIT 1) AS last_order
FROM customers
WHERE exists (SELECT 1 FROM orders
  WHERE customers.id = orders.customer_id);

Also, you can achieve the required result with a simple Join query as the following:

 Select C.id,C.firstname, C.lastname, Max(O.timestamp_) AS last_order
 From customers C
 join orders O on
 C.id=O.customer_id
 Group By C.id,C.firstname, C.lastname

See a demo from db-fiddle.