'how Inner join work on two foreign key from single table

I am working on Bus route management system , I made two table first one is Cities and second one is route have following queries

CREATE TABLE Cities 
    ( 
      ID NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
      Name Varchar(30) not null,
)
CREATE TABLE route
    ( 
      ID NUMBER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
      Name Varchar(30) not null,
      from NUMBER not null,
      to NUMBER NOT NULL,
      CONSTRAINT FROM_id_FK FOREIGN KEY(from) REFERENCES Cities(ID),
      CONSTRAINT TO_id_FK FOREIGN KEY(to) REFERENCES Cities(ID),
)

i am joining the table through inner join

select CITIES.Name  
from CITIES
inner join ROUTES on CITIES.ID=ROUTES.ID

but it show single column as

   Name                        
-----------

but i want result as

    from  |     to     
------------------------

what is possible way to do this using inner join

sql


Solution 1:[1]

Hope is working for you

select CITIES.Name,ROUTES.from,ROUTES.to 
from CITIES inner join ROUTES on CITIES.ID=ROUTES.ID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SV_

Related Questions