T-SQL Select join with condition
23,065
Solution 1
There are several ways that you can get the result. You can use a subquery to get the max(modificationdate)
:
select c.id, r.colorname
from cars c
inner join CarColorhistory h1
on c.id = h1.carid
inner join
(
select max(modificationdate) MaxDate,
carid
from CarColorhistory
group by carid
) h2
on h1.carid = h2.carid
and h1.modificationdate = h2.maxdate
inner join color r
on h1.colorid = r.id
Or since you are using SQL Server you can use ranking functions:
select id, colorname
from
(
select c.id, r.colorname,
row_number() over(partition by c.id order by modificationdate desc) rn
from cars c
inner join CarColorhistory h1
on c.id = h1.carid
inner join color r
on h1.colorid = r.id
) src
where rn = 1;
Solution 2
Try this:
select c.id, colorname
from cars c
inner join CarColorHistory h on c.id = h.CarID
inner join Color c2 on h.colorid = c2.id
where h.ModificationDate = (select max(ModificationDate)
from CarColorHistory x where c.id = x.CarId)
Solution 3
This should do the trick for you:
SELECT c.id, (
SELECT co.ColorName FROM Color co
WHERE co.id = (
SELECT TOP 1 ColorID FROM CarColorHistory
WHERE CarID = c.id
ORDER BY ModificationDate DESC
)
) AS ColorName
Author by
Ellbar
Updated on March 27, 2020Comments
-
Ellbar about 4 years
Lets say that i have a 3 tables:
Cars
- Id
CarColorHistory
- Id
- CarID
- ColorID
- ModificationDate
Color:
- Id
- ColorName
I want to select all cars and their colors but the important thing is, that color for the car is the last modified color from
CarColorHistory
table.I need to use join to do this.
Example:
Cars:
1 2
CarColorhistory:
1 1 1 26/03/2012 -> (actual color, can be take by date or id) 2 1 2 25/03/2012 3 2 2 25/03/2012
Color:
1 Blue 2 Red
I need to get result: (car id, colorName)
1 Blue 2 Red
I tried make it by joining Cars table and CarColorHistory table but I get cars for all colors. I need only actual color (last added).
Please help