CODEWITHSUNDEEP
sqlsql-serverjoinleft-joinsql-query-store
iman
iman

Reputation: 173

left join two tables on a non-unique column in right table

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

Upvotes: 7

Views: 21014

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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;

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Anand
Anand

Reputation: 1123

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
       )

Upvotes: 1

Related Questions