Get list of APIs using 3 table joins-CodePudding

I have 3 tables from which I would like to get some data:

Main table:

    create table if not exists exchange
    (
        id bigint,
        name varchar(255),
    );
    
    INSERT INTO exchange (id, name) 
    VALUES (1, 'PARIS');

Second table:

    create table exchange_api
    (
        id                           bigint,
        api_name                     varchar(255),
        exchange_id                  bigint
    );

INSERT INTO exchange_api (id, api_name, exchange_id)
   VALUES (8, 'PARIS-API', 5);

Third table:

create table instrument
(
    id               bigint,
    symbol           varchar(255),
    exchange_api_id  bigint
);

INSERT INTO instrument (id, symbol, exchange_api_id)
VALUES (47, 'PARIS-ARD', 8);
INSERT INTO instrument (id, symbol, exchange_api_id)
VALUES (48, 'PARIS-SRD', 8);

I need to map the the tables following way:

First table exchange I want to send param PARIS

and as a result I want to get this result:

PARIS-ARD
PARIS-SRD

How I can get this data using JOIN?

CodePudding user response：

Select I.symbol from instrument I 
inner join exchange_api API 
On I.exchange_api_id API.id
Inner JOIN exchange E
ON API.exchange_id = E.id
where E.name = 'PARIS'

If you want pass Pass 'PARIS' as value then it will return

PARIS-ARD
PARIS-SRD

as output

CodePudding user response：

SELECT 
instrument.symbol           
FROM exchange
JOIN exchange_api
ON exchange.id = exchange_api.exchange_id  
JOIN instrument
ON exchange_api.id = instrument.exchange_api_id  
WHERE exchange.name = 'PARIS'

And with ALIAS:

SELECT 
inst.symbol as I         
FROM exchange AS e
JOIN exchange_api AS ea
ON e.id = ea.exchange_id  
JOIN instrument inst
ON ea.id = inst.exchange_api_id  
WHERE e.name = 'PARIS'