[jens]

Hi!

Need help on a SELECT that I can't figure out.

Two tables (simplified here but serves as an example)

TAXIS
-----
ID, int
DriverName, string
DeparturePlaceID, int
ArrivalPlaceID, int

PLACES
------
ID, int
Street, string
City, string

A taxi driven by Carl picks up a person on LargeStreet in LargeCity and drives the person to FunStreet in FunTown

The tables could look like this:

TAXIS
-----
...
2, Carl, 5, 9
3, Bill, 0, 0
...

PLACES
------
...
5, LargeStreet, LargeCity
9, FunStreet, FunTown
...

How do I get the information about who drove from one place to another?
In case someone (like Bill) did not drive anyone anywhere (no values or 0 on DeparturePlaceID and/or ArrivalPlaceID or no matching records in PLACES) I still want Bill to be listed.

I tried select * from TAXIS left outer join PLACES left outer join PLACES which is obviously wrong but I really don't get how to construct this.

This post has been edited by jens: 28 March 2010 - 09:07 AM

[jens]

Sorry, problem solved.

This is what I came up with after using a graphical tool I didn't think of first....

SELECT TAXIS.DriverName, PLACES.City, PLACES_1.City AS Expr1
FROM TAXIS LEFT OUTER JOIN
PLACES ON TAXIS.DeparturePlaceID = PLACES.ID LEFT OUTER JOIN
PLACES AS PLACES_1 ON TAXIS.ArrivalPlaceID = PLACES_1.ID

Regards
Jens