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?
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
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'