CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
CnlPete
CnlPete

Reputation: 25

SQL LEFT JOIN in history table

I prepared an sqlFiddle, located here: http://sqlfiddle.com/#!2/951c1/2/1

I have 3 tables (cars, colors, and car_color_history). In colors there are the colors with some kind of metadata I am interested in. In cars, there are a bunch of cars with a 'wanted_color' key which tells the car to change to the given color. In car_color_history, there are all the entries, on what specific time a car had what color.

What I want now is a view/select statement, to get all cars with the current and the wanted color information.

My first idea was to do this with left joins and some kind of GROUP BY magic, but up until now, I could not get that to work

A working solution is the second select in the fiddle with subselects. Am I correct that that is not a pretty solution as it is slow? Plus i do have to provide the subselect for each displayed column :(

Anybody have a solution?

Upvotes: 1

Views: 292

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125344

SQL Fiddle

select
    ca.carkey,
    description,
    cow.other_info as wanted_color,
    cco.other_info as current_color,
    cch.datetime as current_color_datetime
from
    cars ca
    left join
    colors cow on ca.wanted_color = cow.colorkey
    left join (
        select distinct on (carkey) *
        from car_color_history
        order by carkey, datetime desc
    ) cch using(carkey)
    left join
    colors cco on cch.colorkey = cco.colorkey

Upvotes: 2

Related Questions