mysql: get two values from subquery

Question

I am trying to use a subquery to retrieve the oldest order for each customer. I want to select email_address, order_id, and order_date

Tables:

customers(customer_id, email_address)

orders(order_id, order_date, customer_id)

What I've tried:

I can get either the order_id or the order_date by doing

SELECT email_address, 
       (SELECT order_date /* or order_id */
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY order_date LIMIT 1)
FROM customers c
GROUP BY email_address;

but if I try to do SELECT order_id, order_date in my subquery, I get the error:

Operand should contain 1 column(s)

When doing as sub-query like in your example, it has to return one column because it becomes a single column in your results. Simple duplicate the query as another column, and change the column it returns. — Sloan Thrasher, Oct 06 '18 at 00:44
@SloanThrasher that was my initial inclination, but the DRY programmer in me was not satisfied! — JED, Oct 06 '18 at 16:37

score 2 · Accepted Answer · answered Oct 06 '18 at 01:02

You can solve this with a JOIN, but you need to be careful to only JOIN to the oldest values for a given customer:

SELECT c.email_address, o.order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id AND
    o.order_date = (SELECT MIN(order_date) FROM orders o2 WHERE o2.customer_id = c.customer_id)

Danilo Lemes · Answer 2 · 2018-10-06T02:31:38.417

0

The JOIN is of your choice. How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?

SELECT o.order_id, c.email_address, o.order_date
FROM customers c
INNER JOIN (
    SELECT order_date, order_id, customer_id
    FROM orders o
    ORDER BY order_date
) as o on o.customer_id = c.customer_id
GROUP BY email_address;

edited Oct 06 '18 at 02:31

answered Oct 06 '18 at 00:47

Danilo Lemes

2,342
1
14
16

1

You would need some kind of limit in the JOIN to retrieve only the oldest order. – Sloan Thrasher Oct 06 '18 at 00:50

score 0 · Answer 3 · answered Oct 06 '18 at 00:48

You could use a JOIN to get the result you want, or modify your query as below:

SELECT email_address, 
    (SELECT order_date
    FROM orders o1
    WHERE o1.customer_id = c.customer_id
    ORDER BY order_date LIMIT 1) as `order_date`,
    (SELECT order_id
    FROM orders o2
    WHERE o2.customer_id = c.customer_id
    ORDER BY order_date LIMIT 1) as `order_id`
FROM customers c
GROUP BY email_address;

mysql: get two values from subquery

Tables:

What I've tried:

3 Answers3