CODEWITHSUNDEEP
mysqlinner-join
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

How to get source and destination city name using inner query in mysql

table_cities - city_id, city_name
table_booking - booking_id, source_city_id, destination_city_id

I want to get Booking_id | source_city_name | destination_city_name as result.

I am trying this:

SELECT * FROM table_bookings
    INNER JOIN table_cities
        ON table_bookings.source_city_id = table_cities.city_id
    INNER JOIN table_cities
        ON table_bookings.destination_city_id = table_cities.city_id;

But its giving Not unique table/alias: 'table_cities'

can someone help?

Upvotes: 0

Views: 218

Answers (1)

DrCopyPaste
DrCopyPaste

Reputation: 4117

You need to add aliases to your query, this is because you join on the table table_bookings twice and select * the table names are ambiguous, so you need to add aliases after your joins to make it clear:

SELECT
         table_bookings.Booking_id,
         sourceCities.source_city_name,
         destinationCities.destination_city_name
    FROM table_bookings
    INNER JOIN table_cities AS sourceCities
        ON table_bookings.source_city_id = table_cities.city_id
    INNER JOIN table_cities AS destinationCities
        ON table_bookings.destination_city_id = table_cities.city_id;

Upvotes: 1

Related Questions