T-SQL Select join with condition

Question

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

Comment. Why do you need an ID on color history? – paparazzo Mar 26 '13 at 14:14 — paparazzo, Mar 26 '13 at 14:14

score 4 · Answer 1 · answered Mar 26 '13 at 14:12

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)

score 3 · Accepted Answer · answered Mar 26 '13 at 14:14

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

score 1 · Answer 3 · answered Mar 26 '13 at 14:10

1

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

answered Mar 26 '13 at 14:10

Mike Perrenoud

66,820
29
157
232

Thanks, but could you help me do this by JOIN ON statement? How can I use your thick with JOIN? Its very important for my teacher. – Ellbar Mar 26 '13 at 14:13

score 0 · Answer 4 · answered Mar 26 '13 at 14:18

One way to do this might be with just using a subquery, like posted before, since you are using t-sql you should also be able to do it with an apply:

SELECT
        Cars.Id, LatestColors.ColorID, LatestColors.ModificationDate
    FROM Cars
    CROSS APPLY (
        SELECT TOP 1
                ColorID, ModificationDate
            FROM CarColorHistory
            WHERE CarID = Cars.ID
            ORDER BY ModificationDate DESC
    ) AS LatestColors

score 0 · Answer 5 · answered Mar 26 '13 at 14:22

You can try this if you have Sql Server 2005 or greater:

You can try here, how Common table expression is working: Sql Fiddle demo

  ;WITH CTE_Cars(CarID, MaxDate)
    AS
    (
    SELECT CarID, MAX(ModificataionDate) AS MaxDate
    FROM CarColorHistory
    GROUP BY CarID
    )
    SELECT CTE_Cars.CarID, Color.ColorName
    FROM 
    CTE_Cars
    INNER JOIN CarColorHistory ON CarColorHistory.CarID = CTE_Cars.CarID AND
    CarColorHistory.ModificataionDate = CTE_Cars.MaxDate
    INNER JOIN Color ON Color.id = CarColorHistory.ColorId

score 0 · Answer 6 · answered Mar 26 '13 at 14:30

I'm not sure if this is the best way, but this is the way I do it. First get the values you want out of the tables using the MAX and then use that result as a table to JOIN in order to eliminate unwanted values.

SELECT c.ID, Color.Color
From Cars c JOIN CarColorHistory h on c.id = h.CarID
     JOIN Color on h.ColorID = Color.ID
     JOIN

    --Create a table with only the latest value and the car ID
    ( 
    SELECT c.ID, Max(h.TimeStamp) as time
    FROM Cars c JOIN CarColorHistory h on c.id = h.CarID
         JOIN Color on h.ColorID = Color.ID
    Group by c.ID --Join the table on latest time to get rid of old timestamps
    ) Max on Max.ID = c.ID and h.TimeStamp=max.time

T-SQL Select join with condition

6 Answers6

Linked