how Inner join work on two foreign key from single table-CodePudding

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

CodePudding user response：

I suspect you need something like the following:

select r.Name, cs.Name SourceCity, cd.Name DestinationCity
from routes r
join cities cs on cs.id = r.from
join cities cd on cd.id = r.to

CodePudding user response：

Hope is working for you

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