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
So if your data in the columns you are joining on are not unique you will get duplicate data in the final table. As we can see the non unique data pulls in the same value from the other table twice. This is a common situation that can cause you to double count data if you are not aware that this is happening.
Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL.
Merging tables by rows However, we can merge two tables even if the column names of one table doesn't match with that of the other. Union returns only the unique records of both the tables.
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn't use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With