[b]Easy Join problem.[/b]

[Naoise]

I have 3 tables each with two fields

tblCar = CarID | CarMake
tlbCarColours = CarID | ColourID
tblColours = ColourID | Colour

tblCar
CarID | CarMake
---------------
1 | Ferrari
2 | Ford


tblCarColours
CarID | ColourID
----------------
1 | 1

tblColours
ColourID | Colour
-----------------
1 | Red

I want to return a result that looks like this

CarID | CarMake | Colour
----------------------------------------
1 | Ferrari | Red
2 | Ford | NULL

My SQL :
SELECT * FROM tblCars a, tblCarColours b, tblColours c
WHERE a.CarID *= b.CarID
AND b.ColourID = c.ColourID

The Error :
Server: Msg 303, Level 16, State 1, Line 1
The table 'tblCarColours' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

I have tried :
SELECT * FROM tblCars a, tblCarColours b, tblColours c
WHERE a.CarID *= b.CarID
AND b.ColourID =* c.ColourID

which works but gives me a lot of replicated results and I want distinct results.

Any ideas??

Thanks for replies

 

[PHV]

And this ?
SELECT *
FROM tblCars a
LEFT JOIN tblCarColours b ON a.CarID = b.CarID
LEFT JOIN tblColours c ON b.ColourID = c.ColourID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244