T-SQL Select join with condition

Question

Lets say that i have a 3 tables:

1. Cars

   • Id

2. CarColorHistory

   • Id
   • CarID
   • ColorID
   • ModificationDate

3. 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

Answer 1

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)

Answer 2

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

See SQL Fiddle with Demo

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;

See SQL Fiddle with Demo