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
– Sloan Thrasher, Commented Oct 6, 2018 at 0:44
@SloanThrasher that was my initial inclination, but the DRY programmer in me was not satisfied! — JED
– JED, Commented Oct 6, 2018 at 16:37

Nick · Accepted Answer · 2018-10-06 01:02:34Z

2

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)

answered Oct 6, 2018 at 1:02

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sloan Thrasher · Accepted Answer · 2018-10-06 00:48:26Z

0

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;

answered Oct 6, 2018 at 0:48

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

Comments

Danilo Lemes · Accepted Answer · 2018-10-06 02:31:38Z

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 6, 2018 at 2:31

answered Oct 6, 2018 at 0:47

Danilo Lemes

2,5181 gold badge16 silver badges16 bronze badges

1 Comment

Sloan Thrasher Over a year ago

You would need some kind of limit in the JOIN to retrieve only the oldest order.

Collectives™ on Stack Overflow

mysql: get two values from subquery

Tables:

What I've tried:

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Tables:

What I've tried:

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related