[SOLVED] Join a table to multiple fields?

[joshmaker]

Ok, I've recently learned the join command, but have run into a situation where I would like to join a single table to multiple fields in another table.  For example, lets say I have a table `travelers`and a table `locations`.  I can join location and travelers in one place like this:

SELECT *
FROM `travelers`
LEFT JOIN `locations` ON `travelers`.`first_destination` = `locations`.`id`	

No problem.  But what if one of my travelers wants to visit many locations?  Trying something like:

 

SELECT *
FROM `travelers`
LEFT JOIN `locations` ON `travelers`.`first_destination` = `locations`.`id`
LEFT JOIN `locations` ON `travelers`.`second_destination` = `locations`.`id`	
LEFT JOIN `locations` ON `travelers`.`third_destination` = `locations`.`id`	

Would give me an error like this one: #1066 - Not unique table/alias: 'locations'

 

How would I join these?  Could someone kindly point my in the proper direction?

[Wildbug]

Yeah, you'll need to "alias" each table.  Since they all have the same name, you need to provide a way to tell each table apart.  Hence, alias ("AS xxxxx").

 

SELECT *
FROM travelers
LEFT JOIN locations AS a ON travelers.first_destination = a.id
LEFT JOIN locations AS b ON travelers.second_destination = b.id	
LEFT JOIN locations AS c ON travelers.third_destination = c.id

[joshmaker]

Thanks!