SQL query with 2x link to other table

[Terminator]

Yeah I know, weird title 🙂

But I have the following problem

Query :

SELECT a.id, a.place1id, a.place2id, a.title FROM routes a, cities b WHERE a.place1id=b.id AND a.place2id=b.id id='" . $_GET['id'] . "'

Above query is not working, but what I want is that to select ALL rows in routes where ID = given id and that I get the place names from cities.

I only got it working with 1 of the placeID's but not with both

Example tables

routes
id, place1id, place2id, title
1 2 1 A title 1

cities
id place
1 Amsterdam
2 Enschede

Then the output from the query would be

1, Enschede, Amsterdam, A Title 1

[djjjozsi]

i think you can do it with double LEFT JOIN:

Select 
  routes.id,
  cities1.place As place1,
  cities.place As place2,
  routes.title
From routes Left Join
  cities cities1 On cities1.id = routes.place1id Left Join
  cities On cities.id = routes.place2id

hello, jjozsi