1 Replies - 2959 Views - Last Post: 28 March 2010 - 08:27 AM Rate Topic: -----

#1 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

SQL select from two tables using second table twice?

Posted 28 March 2010 - 08:10 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: SQL select from two tables using second table twice?

#2 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: SQL select from two tables using second table twice?

Posted 28 March 2010 - 08:27 AM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1