SQL add more columns in inner join?

Question

I have two tables:

customers
id	name	order_id

orders
id	customer_id	date	amount

I would like to get the first order for each customer so I used inner join & min on date. however, I'd like to get all order columns as well.

My SQL query is

SELECT 
    customers.id, customers.name, 
    MIN(orders.date) 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY   
    customers.id;

When I try to add more columns from orders table as following:

SELECT 
    customers.id, customers.name, 
    MIN(orders.date), orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY 
    customers.id;

I get an error

ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

score 2 · Accepted Answer · answered Sep 13 '22 at 07:30

Use distinct on w/o group by. You may see this SO discussion for details.

SELECT distinct on (customers.id)
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
ORDER BY BY 
    customers.id, orders.date;

score 1 · Answer 2 · answered Sep 13 '22 at 05:06

select oo.*,c.*,fo.* from 
(select o.customer_id,min(o.date)order_date 
from orders o
group by o.customer_id)fo
inner join orders oo on oo.customer_id=fo.customer_id and oo.date=fo.order_date
inner join customer c on c.id=oo.customer_id

I think this will help you. In SQL Group by clause, you can only select grouped column and aggregated columns. For your case, you have to add extra join to take whole data from order table using customer_id and minimun order_date

score 1 · Answer 3 · answered Sep 19 '22 at 07:30

Then don't use min at the main clause.

min(orders.date) means that you want to get the minimum date for one column. You can filter order first before joining

SELECT 
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id 
INNER JOIN (
        SELECT customer_id, Min(date) as MinDate 
        FROM orders 
        GROUP BY customer_id
    ) filtered_order 
        ON filtered_order.customer_id = customers.id 
        AND filtered_order.MinDate = orders.date

SQL add more columns in inner join?

3 Answers3