how Inner join work on two foreign key from single table

Muhammad Adeel

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

Stu

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

inner join two foreign key in one table

INNER JOIN for two columns of the same foreign key

Two FOREIGN key constraints from single parent table not working

How to make inner join two times to single table in sql server

How to combine rows from two tables into a single table based on Foreign key, in MySQL.?

how to join two table only when foreign key value is not null

How to migrate IDs from JOIN table into foreign key column in PostgreSQL

How to migrate IDs from JOIN table into foreign key column in MySQL

How to update a table that is built from an inner join of two other tables

Select distinct on foreign key, inner join another table

join with two columns and get field values from their foreign key with single sql query

Is it possible to display other column names from two table to one table by using join clause by using foreign key

How to "inner join" two arrays by a key in javascript?

How to name two columns in join tables with two same foreign key at one table in SQL?

Join two table with two different Foreign Key for get same field

Django: join two table on foreign key to third table?

How to make an Inner Join in django without foreign key?

Two fields with foreign key from a table - Rails

MySQL two foreign key from same table

How to inner join two tables to junction table

How to perform a MYSQL join on two tables where the condition for the value of the foreign key to the second table could also be null

how to add two fields as foreign key to a table?

MYSQL: How to join two tables using Inner join and then calculatin the total number from the second table for the following examples

Delete data from a single table from inner join result

How to join a table with itself based on a foreign key in same table?

select single row from foreign table in left join

sql join with foreign key table

reverse foreign key join - select from two tables

Join column from another table with Foreign Key using JPA

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive