I couldn't find the answer yet.
My db tables are like that:
- routes - table for routes, with columns:
- destinations - table for all the destinations, with columns:
Because every destination is also departure point I use one table for both.
Relation is as follows :
1. routes.destination_id = destinations.destination_id,
2. routes.departure_id = destinations.destination_id
The result I need is as described:
If routes.destination_id = 3 and routes.departure_id = 5, I want to match each of them to the corresponds name in the destinations table, and to put in the result the correct column names, for departure I want to call it departure_city and same for destination. Right now I get the same name for both different ids, so if destination_id #3 is New York and destination_id #5 is Las Vegas, I will get both as New York and that is not what I need.
So far, my query looks like that:
Before you are telling me to use JOIN, I have done it with JOIN but couldn't change the column name! Also, I always received all the table's columns and I do not know how to get only necessary columns with JOIN!
SELECT routes.*, destinations.destination_name as 'departure_city', destinations.destination_name as 'destination_city'
FROM routes, destinations
WHERE routes.route_departure_id = destinations.destination_id
Right now my issues are that I can't get the value from destinations twice, once for the route_destination and one for the route_departure and show different values.
Please let me know if I my question isn't clear enough and I will try to respond as fast as I can, this is really urgent.
Thank you very much in advance!