CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
wra
wra

Reputation: 257

SQL: How to join the same table multiple times?

I have two tables, and I need to join the second table two times for two different columns. The tables are in the following format:

Table 1: trip_details

column            type
name              string
start_country_id  int
end_country_id    int

Table 2: country_info

column   type
id       int
country  string

I would like to get the Name, Start Country, and End Country.

This would be my attempt at it:

SELECT
trip_details.name AS "Name",
country_info.country AS "Start Country",
country_info.country AS "End Country"

FROM
trip_details

LEFT JOIN country_info ON country_info.id = trip_details.start_country_id
LEFT JOIN country_info ON country_info.id = trip_details.end_country_id

From what I see, the problem is with the join since I'm using "country_info.country" twice in my Select clause. What is the best way/practices for these situations?

EDIT:
Not sure if there are other ways of doing this, but this is only part of my SQL query so I do need to use a LEFT JOIN

Upvotes: 3

Views: 11627

Answers (1)

Mureinik
Mureinik

Reputation: 311088

Having two join clauses is the right way to go. You're just missing giving them different aliases in order to distinguish between the two:

SELECT    td.name AS "Name",
          sci.country AS "Start Country",
          eci.country AS "End Country"
FROM      trip_details td
LEFT JOIN country_info sci ON sci.id = td.start_country_id
LEFT JOIN country_info eci ON eci.id = td.end_country_id

Upvotes: 6

Related Questions