I have these tables in my database, namely "airport" and "route", the id of "airport" is a foreign key in "route" (i.e. Origin, Destination).
Airport
+-------+-------------+-----------------------+
| id | airportcode | Location |
+-------+-------------+-----------------------+
| 1 | CEB | Cebu |
| 2 | MAN | Manila |
+-------+-------------+-----------------------+
Routes
+-------+-------------+-----------------------+
| id | Origin | Destination |
+-------+-------------+-----------------------+
| 1 | 1 | 2 |
| 2 | 2 | 1 |
+-------+-------------+-----------------------+
So far, this is my query in my Controller and it's only returning the "Origin, Destination"
DB::table('airport')
->join('route', 'airport.id','=','route.Origin')
->join('route', 'airport.id','=','route.Destination')
->select('route.Origin', 'route.Destination')
->get();
What I would like to do is this:
SELECT 'airport.Location' from airport, route WHERE 'route.Origin' = 'airport.id' AND 'route.Destination' = 'airport.id"
.
Any suggestions will do!