Eliran Levi Eliran Levi - 2 years ago 60
SQL Question

How to add same column twice to table in different places?

I couldn't find the answer yet.

My db tables are like that:

  1. routes - table for routes, with columns:

    a. destination_id
    b. departure_id

  2. destinations - table for all the destinations, with columns:

    a. destination_id
    b. destination_name.

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:

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

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!

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!

Answer Source

You are looking for two joins:

SELECT r.*, 
       dep.destination_name as departure_city, 
       dest.destination_name as destination_city
FROM routes r JOIN
     destinations dep
     ON r.departure_id = dep.destination_id JOIN
     destinations dest
     ON r.destination_id = dest.destination_id;

Additional advice:

  • Use table aliases. They make the query easier to write and to read.
  • Never use commas in the FROM clause. Always use explicit JOIN syntax.
  • Do not use single quotes to define column aliases. Only use single quotes for string and date constants. Use backticks if you are using a name that needs to be quoted (and then, change the name so backticks aren't necessary).
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download