left join two tables on a non-unique column in right table
13,185
Solution 1
Use the not exists
clause for the win!
select c.customer, p.*
from Customer as c
inner join Purchase as p
on p.customer_id = c.id
where not exists (
select 1
from Purchase as p2
where p2.customer_id = p.customer_id
and p2.date > p.date
)
Solution 2
If you just want the max date, use aggregation. I would recommend a left join
for customers who have made no purchases:
select c.customer, c.id, max(p.date)
from customers c left join
purchases p
on c.id = p.customer_id
group by c.customer, c.id;
Solution 3
I think you can use inner join and group by
select table1.customer, table1.id, table.max(date)
from table1
inner join table2 on table1.id = table2.id
group by table1.customer, table1.id
Author by
iman
Updated on June 04, 2022Comments
-
iman almost 2 years
I have two tables in sql server and i wanna select and join some data from these table.the first tables have some customer like:
--------------- customer id Dave 1 Tom 2 ---------------
and second table i table of purchases that includes list of last purchases with cost and which customer bought that Product:
------------------ product date customer id PC 1-1-2000 1 phone 2-3-2000 2 laptop 3-1-2000 1 ------------------
i wanna select first table (customers info) with last date of their purchases! i tried left join but that doesn't give me last purchases becuase customer id is not unique in second table! how can i do this function with SQL server query? Regards
-
iman almost 8 yearsthis worked, but actually when i was completing my code i faced a problem. i have another column (seller) in purchases table, when i add p.Seller to select clause the left join does not work and select few more rows from p table. how can i get seller of the max(p.date) although?
-
Gordon Linoff almost 8 years@iman . . . Ask another question, along with appropriate sample data and desired results.