SQL : How to fetch data from second table based on ID from two columns in first table?
Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I need to fetch corresponding station name from second table (stations) for the start and end station id in first table(trips). How to write the condition to match id with its corresponding name(see query below)?
select
tp.id as Trip ID, st.station_name as Start Station,
st.station_name as End Station, en.entity_type as Subscriber Type
from
escooter_trips tp, escooter_stations st, escooter_entity en
where
tp.start_station_id = st.id,
tp.end_station_id = st.id,
tp.entity_id = en.id;
sql
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
add a comment |
0
I need to fetch corresponding station name from second table (stations) for the start and end station id in first table(trips). How to write the condition to match id with its corresponding name(see query below)?
select
tp.id as Trip ID, st.station_name as Start Station,
st.station_name as End Station, en.entity_type as Subscriber Type
from
escooter_trips tp, escooter_stations st, escooter_entity en
where
tp.start_station_id = st.id,
tp.end_station_id = st.id,
tp.entity_id = en.id;
sql
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
1
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 17 '18 at 7:18
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20
add a comment |
0
0
0
I need to fetch corresponding station name from second table (stations) for the start and end station id in first table(trips). How to write the condition to match id with its corresponding name(see query below)?
select
tp.id as Trip ID, st.station_name as Start Station,
st.station_name as End Station, en.entity_type as Subscriber Type
from
escooter_trips tp, escooter_stations st, escooter_entity en
where
tp.start_station_id = st.id,
tp.end_station_id = st.id,
tp.entity_id = en.id;
sql
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
I need to fetch corresponding station name from second table (stations) for the start and end station id in first table(trips). How to write the condition to match id with its corresponding name(see query below)?
select
tp.id as Trip ID, st.station_name as Start Station,
st.station_name as End Station, en.entity_type as Subscriber Type
from
escooter_trips tp, escooter_stations st, escooter_entity en
where
tp.start_station_id = st.id,
tp.end_station_id = st.id,
tp.entity_id = en.id;
sql
sql
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
edited Nov 17 '18 at 7:18
marc_s
586k13011281272
586k13011281272
asked Nov 17 '18 at 3:59
NGBNGB
371211
asked Nov 17 '18 at 3:59
NGBNGB
371211
asked Nov 17 '18 at 3:59
NGBNGB
371211
371211
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
1
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 17 '18 at 7:18
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20
add a comment |
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
1
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 17 '18 at 7:18
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
1
1
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 17 '18 at 7:18
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 17 '18 at 7:18
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20
add a comment |
2 Answers
2
active
oldest
votes
1
You have to join escooter_stations 2 times.
select tp.id as Trip ID,
st_start.station_name as Start Station,
st_end.station_name as End Station,
en.entity_type as Subscriber Type
from escooter_trips tp
left join escooter_stations st_start on tp.start_station_id = st_start.id
left join escooter_stations st_end on tp.end_station_id = st_end.id
left join escooter_entity en on tp.entity_id = en.id;
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
add a comment |
1
You will need to use left outer join 2 times, one for start, one for end, like below:
select et.id,es_start.station_name as [StartStationName],es_end.station_name as [EndStationName]
from escooter_trips et
left outer join escooter_stations es_start on es.id = et.start_station_id
left outer join escooter_stations es_end on es.id = et.end_station_id
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53348074%2fsql-how-to-fetch-data-from-second-table-based-on-id-from-two-columns-in-first%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You have to join escooter_stations 2 times.
select tp.id as Trip ID,
st_start.station_name as Start Station,
st_end.station_name as End Station,
en.entity_type as Subscriber Type
from escooter_trips tp
left join escooter_stations st_start on tp.start_station_id = st_start.id
left join escooter_stations st_end on tp.end_station_id = st_end.id
left join escooter_entity en on tp.entity_id = en.id;
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
add a comment |
1
You have to join escooter_stations 2 times.
select tp.id as Trip ID,
st_start.station_name as Start Station,
st_end.station_name as End Station,
en.entity_type as Subscriber Type
from escooter_trips tp
left join escooter_stations st_start on tp.start_station_id = st_start.id
left join escooter_stations st_end on tp.end_station_id = st_end.id
left join escooter_entity en on tp.entity_id = en.id;
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
add a comment |
1
1
1
You have to join escooter_stations 2 times.
select tp.id as Trip ID,
st_start.station_name as Start Station,
st_end.station_name as End Station,
en.entity_type as Subscriber Type
from escooter_trips tp
left join escooter_stations st_start on tp.start_station_id = st_start.id
left join escooter_stations st_end on tp.end_station_id = st_end.id
left join escooter_entity en on tp.entity_id = en.id;
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
You have to join escooter_stations 2 times.
select tp.id as Trip ID,
st_start.station_name as Start Station,
st_end.station_name as End Station,
en.entity_type as Subscriber Type
from escooter_trips tp
left join escooter_stations st_start on tp.start_station_id = st_start.id
left join escooter_stations st_end on tp.end_station_id = st_end.id
left join escooter_entity en on tp.entity_id = en.id;
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
answered Nov 17 '18 at 4:09
DanBDanB
1,7601315
1,7601315
add a comment |
add a comment |
1
You will need to use left outer join 2 times, one for start, one for end, like below:
select et.id,es_start.station_name as [StartStationName],es_end.station_name as [EndStationName]
from escooter_trips et
left outer join escooter_stations es_start on es.id = et.start_station_id
left outer join escooter_stations es_end on es.id = et.end_station_id
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
add a comment |
1
You will need to use left outer join 2 times, one for start, one for end, like below:
select et.id,es_start.station_name as [StartStationName],es_end.station_name as [EndStationName]
from escooter_trips et
left outer join escooter_stations es_start on es.id = et.start_station_id
left outer join escooter_stations es_end on es.id = et.end_station_id
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
add a comment |
1
1
1
You will need to use left outer join 2 times, one for start, one for end, like below:
select et.id,es_start.station_name as [StartStationName],es_end.station_name as [EndStationName]
from escooter_trips et
left outer join escooter_stations es_start on es.id = et.start_station_id
left outer join escooter_stations es_end on es.id = et.end_station_id
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
You will need to use left outer join 2 times, one for start, one for end, like below:
select et.id,es_start.station_name as [StartStationName],es_end.station_name as [EndStationName]
from escooter_trips et
left outer join escooter_stations es_start on es.id = et.start_station_id
left outer join escooter_stations es_end on es.id = et.end_station_id
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
answered Nov 17 '18 at 4:09
Eray BalkanliEray Balkanli
4,65852347
4,65852347
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53348074%2fsql-how-to-fetch-data-from-second-table-based-on-id-from-two-columns-in-first%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
7846RA dJMQm,ZK0et5Wp8fRYglumS,pPjad1,BR MrP5Uw53IVw0qqwLb5PZm1lcp 23,PNL Wv,H
What is wrong with your query? Can you provide data sample, and expected result? Which DBMS are you using? I would suggest you to use JOIN syntax instead of listing tables in FROM statement.
– DanB
Nov 17 '18 at 4:04
I'm not sure about using join statement. Issue is that st.station_name should fetch the name according to the tp.start_station_id or tp.end_station_id. Using Oracle DB.
– NGB
Nov 17 '18 at 4:06
1
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 17 '18 at 7:18
Is any of the solution worked?
– DanB
Nov 19 '18 at 3:20