Parent child relationship Join tables in oracle sql
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a requirement below.
Now I have to get output like below
How can this be achieved ?
I have written the below SQL but parent_position_id is coming, not parent_position_code
select
hapf.position_code,
pphf.parent_position_id
from
hr_all_positions_f hapf, PER_POSITION_HIERARCHY_F pphf
where
hapf.position_id = pphf.position_id
Should I write a sub query? How should I proceed ?
This is Oracle SQL
Thanks,
Shivam
sql oracle join oracle11g
asked Nov 26 '18 at 13:24
shivamshivam
132519
add a comment |
I have a requirement below.
Now I have to get output like below
How can this be achieved ?
I have written the below SQL but parent_position_id is coming, not parent_position_code
select
hapf.position_code,
pphf.parent_position_id
from
hr_all_positions_f hapf, PER_POSITION_HIERARCHY_F pphf
where
hapf.position_id = pphf.position_id
Should I write a sub query? How should I proceed ?
This is Oracle SQL
Thanks,
Shivam
sql oracle join oracle11g
asked Nov 26 '18 at 13:24
shivamshivam
132519
Hint:JOIN. AndJOINagain.
– Gordon Linoff
Nov 26 '18 at 13:46
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04
add a comment |
I have a requirement below.
Now I have to get output like below
How can this be achieved ?
I have written the below SQL but parent_position_id is coming, not parent_position_code
select
hapf.position_code,
pphf.parent_position_id
from
hr_all_positions_f hapf, PER_POSITION_HIERARCHY_F pphf
where
hapf.position_id = pphf.position_id
Should I write a sub query? How should I proceed ?
This is Oracle SQL
Thanks,
Shivam
sql oracle join oracle11g
asked Nov 26 '18 at 13:24
shivamshivam
132519
I have a requirement below.
Now I have to get output like below
How can this be achieved ?
I have written the below SQL but parent_position_id is coming, not parent_position_code
select
hapf.position_code,
pphf.parent_position_id
from
hr_all_positions_f hapf, PER_POSITION_HIERARCHY_F pphf
where
hapf.position_id = pphf.position_id
Should I write a sub query? How should I proceed ?
This is Oracle SQL
Thanks,
Shivam
sql oracle join oracle11g
sql oracle join oracle11g
asked Nov 26 '18 at 13:24
shivamshivam
132519
asked Nov 26 '18 at 13:24
shivamshivam
132519
asked Nov 26 '18 at 13:24
shivamshivam
132519
asked Nov 26 '18 at 13:24
shivamshivam
132519
asked Nov 26 '18 at 13:24
shivamshivam
132519
132519
Hint:JOIN. AndJOINagain.
– Gordon Linoff
Nov 26 '18 at 13:46
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04
add a comment |
Hint:JOIN. AndJOINagain.
– Gordon Linoff
Nov 26 '18 at 13:46
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04
Hint:
JOIN. And JOIN again.– Gordon Linoff
Nov 26 '18 at 13:46
Hint:
JOIN. And JOIN again.– Gordon Linoff
Nov 26 '18 at 13:46
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04
add a comment |
1 Answer
1
active
oldest
votes
Noone ever said you could only join a table in once:
select
chi.position_code,
par.position_code as parent_position_code
from
hr_all_positions_f hapf
INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id
Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times... Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses - you'd join the addresses table to the Student table 3 times to get all the data..
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
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
});
}
});
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%2f53482107%2fparent-child-relationship-join-tables-in-oracle-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Noone ever said you could only join a table in once:
select
chi.position_code,
par.position_code as parent_position_code
from
hr_all_positions_f hapf
INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id
Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times... Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses - you'd join the addresses table to the Student table 3 times to get all the data..
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
add a comment |
Noone ever said you could only join a table in once:
select
chi.position_code,
par.position_code as parent_position_code
from
hr_all_positions_f hapf
INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id
Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times... Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses - you'd join the addresses table to the Student table 3 times to get all the data..
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
add a comment |
Noone ever said you could only join a table in once:
select
chi.position_code,
par.position_code as parent_position_code
from
hr_all_positions_f hapf
INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id
Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times... Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses - you'd join the addresses table to the Student table 3 times to get all the data..
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
Noone ever said you could only join a table in once:
select
chi.position_code,
par.position_code as parent_position_code
from
hr_all_positions_f hapf
INNER JOIN PER_POSITION_HIERARCHY_F chi on hapf.position_id = chi.position_id
INNER JOIN PER_POSITION_HIERARCHY_F par on hapf.parent_position_id = par.position_id
Bear it in mind; I see people coming to thinking all the time that they can only join a table once. If one table decodes a value in 3 different columns, then you sure can join that same table in 3 times... Imagine if it were an address table, and a Student had a HomeAddressId, WorkAddressId and StudyAddressId, and the Address table held all these addresses - you'd join the addresses table to the Student table 3 times to get all the data..
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
answered Nov 26 '18 at 14:04
Caius JardCaius Jard
12.5k21440
12.5k21440
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
add a comment |
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
Thanks for detailed reply, this is a easy way to do it than I was doing it.
– shivam
Nov 26 '18 at 14:24
add a comment |
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.
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%2f53482107%2fparent-child-relationship-join-tables-in-oracle-sql%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
Hint:
JOIN. AndJOINagain.– Gordon Linoff
Nov 26 '18 at 13:46
Can you please tell me how these joins would work ?
– shivam
Nov 26 '18 at 13:58
I got it, I used this SQL query as a table to join in another query. I think this is correct
– shivam
Nov 26 '18 at 14:04