CODEWITHSUNDEEP
mysql
JED
JED

Reputation: 1694

mysql: get two values from subquery

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)

Upvotes: 0

Views: 783

Answers (3)

Danilo Lemes
Danilo Lemes

Reputation: 2488

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;

Upvotes: 0

Nick
Nick

Reputation: 147166

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)

Upvotes: 2

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

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;

Upvotes: 0

Related Questions