Re: select with multiple references

Michael Werber wrote:
>
> color:
> id color
> -----------
> 1 red
> 2 yellow
> 3 green
>
> trafficlight:
> id idColor1 idColor2 someOtherColumn
> --------------------------------------------
> 1 2 3 sometext
> 2 3 3 sometexttoo
> 3 3 1 somemoretext
> : : : :
>
> [...]
>
> what i want to do is a select wich gives me all records of the
> trafficlight-table but instead of the numbers i want to see the
> colornames. i mean the result of the select-statement should look
> like:
>
> selectstatement:
> someOtherColumn Light1 Light2
> -----------------------------
> someText yellow green
> someTextToo green green
> someMoreText green red

SELECT t.someOtherColumn, c1.color AS Light1, c2.color AS Light2 FROM trafficlight AS t, color AS c1, color AS c2 WHERE t.idColor1 = c1.id AND t.idColor2 = c2.id;

Kind regards,

-- 
  Jan Hidders