How can I get these two suburbs with SQL?

I want to get a departure and destination suburb in my SQL.

SELECT jp.`id`,
     CONCAT(u.`first_name`, " ", u.`last_name`) AS `name`,
     jp.`departure_time`,
     jp.`destination_time`,
     s.`suburb` AS `departure_suburb`


FROM `journey_planning` AS `jp`
JOIN `users` AS `u` ON jp.`driver_id` = u.`id`
JOIN `suburbs` AS s ON jp.`departure_suburb_id` = s.`id`
  OR jp.`destination_suburb_id` = s.`id`

As you can see, there is a departure_suburb_id and destination_suburb_id. I need them to map to their relative value in the suburbs table.

At the moment, this is returning two rows, both w开发者_如何学Goith identical information except the different suburb name (however both as departure_suburb, obviously).

I'm no SQL expert, so I'm not sure what I should be doing. I thought about using subqueries, but I thought there was probably a way without them and avoiding two lookups.

How should I modify this query to return what it is now, though with departure_suburb and destination_suburb pointing to the right values in the suburbs table?

You need to join to the suburbs table twice, once for departure, once for destination. Also it's a good habit to be in to type the type of join you're using, even though "INNER JOIN" is implied!

SELECT jp.`id`,
     CONCAT(u.`first_name`, " ", u.`last_name`) AS `name`,
     jp.`departure_time`,
     jp.`destination_time`,
     s.`suburb` AS `departure_suburb`
     s2.`suburb` AS `destination_suburb` 

FROM `journey_planning` AS `jp`
JOIN `users` AS `u` ON jp.`driver_id` = u.`id`
JOIN `suburbs` AS s ON jp.`departure_suburb_id` = s.`id`
JOIN `suburbs` AS s2 ON jp.`destination_suburb_id` = s2.`id`

SELECT
    jp.id
  , CONCAT(u.first_name, " ", u.last_name) AS name
  , jp.departure_time
  , jp.destination_time
  , dep.suburb AS departure_suburb
  , dest.suburb AS destination_suburb

FROM journey_planning AS jp
  JOIN users AS u 
    ON jp.driver_id = u.id
  JOIN suburbs AS dep 
    ON jp.departure_suburb_id = dep.id
  JOIN suburbs AS dest 
    OR jp.destination_suburb_id = dest.id

How can it be done otherwise? (I wouldn't recommend this for a query with simple JOINs like yours). Since all the relationships are 1-to-many with many on the jp table, it can be done like this:

SELECT
    jp.id
  , CONCAT( ( SELECT first_name
              FROM users
              WHERE jp.driver_id = users.id
            )
          , " "
          , ( SELECT last_name
              FROM users
              WHERE jp.driver_id = users.id
            )
          )
      AS name
  , jp.departure_time
  , jp.destination_time
  , ( SELECT suburb
      FROM suburbs
      WHERE jp.departure_suburb_id = suburbs.id
    ) 
      AS departure_suburb
  , ( SELECT suburb
      FROM suburbs
      WHERE jp.destination_suburb_id = suburbs.id
    ) 
      AS destination_suburb

FROM journey_planning AS jp

As you can see, it gets quite complicated, especially when you want to show more than one field from a table, like the first and last name from table users.

Just JOIN suburbs again with a different table alias:

SELECT jp.`id`,
    CONCAT(u.`first_name`, " ", u.`last_name`) AS `name`,
    jp.`departure_time`,
    jp.`destination_time`,
    departure_suburb.`suburb` AS `departure_suburb`,
    destination_suburb.`suburb` AS `destination_suburb`
FROM `journey_planning` AS `jp`
    JOIN `users` AS `u` ON jp.`driver_id` = u.`id`
    JOIN `suburbs` AS depart_suburb ON jp.`departure_suburb_id` = s.`id`
    JOIN `suburbs` AS destination_suburb ON jp.`destination_suburb_id` = ss.`id`