Mixing
SQL - Transitive relationships
Let's say I have two tables 'Rel' and 'Workers'.
I want to list the names as pairs of those people who are in a transitive relationship.
So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are
in such a relationship. So I want their names to be listed in my result table.
I tried using joins and 'connect by' to solve this but kept failing. Hope you guys can help.
sql oracle
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
add a comment |
Let's say I have two tables 'Rel' and 'Workers'.
I want to list the names as pairs of those people who are in a transitive relationship.
So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are
in such a relationship. So I want their names to be listed in my result table.
I tried using joins and 'connect by' to solve this but kept failing. Hope you guys can help.
sql oracle
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31
add a comment |
Let's say I have two tables 'Rel' and 'Workers'.
I want to list the names as pairs of those people who are in a transitive relationship.
So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are
in such a relationship. So I want their names to be listed in my result table.
I tried using joins and 'connect by' to solve this but kept failing. Hope you guys can help.
sql oracle
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
Let's say I have two tables 'Rel' and 'Workers'.
I want to list the names as pairs of those people who are in a transitive relationship.
So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are
in such a relationship. So I want their names to be listed in my result table.
I tried using joins and 'connect by' to solve this but kept failing. Hope you guys can help.
sql oracle
sql oracle
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
asked Nov 19 '18 at 20:59
user3367856user3367856
621111
621111
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31
add a comment |
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31
add a comment |
1 Answer
1
active
oldest
votes
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Rel ( Employee, Boss ) AS
SELECT 100, 105 FROM DUAL UNION ALL
SELECT 177, 180 FROM DUAL UNION ALL
SELECT 105, 911 FROM DUAL UNION ALL
SELECT 400, 402 FROM DUAL;
CREATE TABLE Workers ( id, name ) AS
SELECT 100, 'A' FROM DUAL UNION ALL
SELECT 105, 'B' FROM DUAL UNION ALL
SELECT 177, 'C' FROM DUAL UNION ALL
SELECT 180, 'D' FROM DUAL UNION ALL
SELECT 400, 'E' FROM DUAL UNION ALL
SELECT 402, 'F' FROM DUAL UNION ALL
SELECT 911, 'G' FROM DUAL;
Query 1:
SELECT e.name as employee,
w.name as related_to
FROM (
SELECT CONNECT_BY_ROOT( Employee ) AS employee,
boss AS related_to
FROM Rel
CONNECT BY PRIOR Boss = Employee
) r
INNER JOIN Workers e
ON e.id = r.employee
INNER JOIN Workers w
ON w.id = r.related_to
Results:
| EMPLOYEE | RELATED_TO |
|----------|------------|
| A | B |
| C | D |
| E | F |
| B | G |
| A | G |
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
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%2f53382534%2fsql-transitive-relationships%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
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Rel ( Employee, Boss ) AS
SELECT 100, 105 FROM DUAL UNION ALL
SELECT 177, 180 FROM DUAL UNION ALL
SELECT 105, 911 FROM DUAL UNION ALL
SELECT 400, 402 FROM DUAL;
CREATE TABLE Workers ( id, name ) AS
SELECT 100, 'A' FROM DUAL UNION ALL
SELECT 105, 'B' FROM DUAL UNION ALL
SELECT 177, 'C' FROM DUAL UNION ALL
SELECT 180, 'D' FROM DUAL UNION ALL
SELECT 400, 'E' FROM DUAL UNION ALL
SELECT 402, 'F' FROM DUAL UNION ALL
SELECT 911, 'G' FROM DUAL;
Query 1:
SELECT e.name as employee,
w.name as related_to
FROM (
SELECT CONNECT_BY_ROOT( Employee ) AS employee,
boss AS related_to
FROM Rel
CONNECT BY PRIOR Boss = Employee
) r
INNER JOIN Workers e
ON e.id = r.employee
INNER JOIN Workers w
ON w.id = r.related_to
Results:
| EMPLOYEE | RELATED_TO |
|----------|------------|
| A | B |
| C | D |
| E | F |
| B | G |
| A | G |
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
add a comment |
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Rel ( Employee, Boss ) AS
SELECT 100, 105 FROM DUAL UNION ALL
SELECT 177, 180 FROM DUAL UNION ALL
SELECT 105, 911 FROM DUAL UNION ALL
SELECT 400, 402 FROM DUAL;
CREATE TABLE Workers ( id, name ) AS
SELECT 100, 'A' FROM DUAL UNION ALL
SELECT 105, 'B' FROM DUAL UNION ALL
SELECT 177, 'C' FROM DUAL UNION ALL
SELECT 180, 'D' FROM DUAL UNION ALL
SELECT 400, 'E' FROM DUAL UNION ALL
SELECT 402, 'F' FROM DUAL UNION ALL
SELECT 911, 'G' FROM DUAL;
Query 1:
SELECT e.name as employee,
w.name as related_to
FROM (
SELECT CONNECT_BY_ROOT( Employee ) AS employee,
boss AS related_to
FROM Rel
CONNECT BY PRIOR Boss = Employee
) r
INNER JOIN Workers e
ON e.id = r.employee
INNER JOIN Workers w
ON w.id = r.related_to
Results:
| EMPLOYEE | RELATED_TO |
|----------|------------|
| A | B |
| C | D |
| E | F |
| B | G |
| A | G |
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
add a comment |
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Rel ( Employee, Boss ) AS
SELECT 100, 105 FROM DUAL UNION ALL
SELECT 177, 180 FROM DUAL UNION ALL
SELECT 105, 911 FROM DUAL UNION ALL
SELECT 400, 402 FROM DUAL;
CREATE TABLE Workers ( id, name ) AS
SELECT 100, 'A' FROM DUAL UNION ALL
SELECT 105, 'B' FROM DUAL UNION ALL
SELECT 177, 'C' FROM DUAL UNION ALL
SELECT 180, 'D' FROM DUAL UNION ALL
SELECT 400, 'E' FROM DUAL UNION ALL
SELECT 402, 'F' FROM DUAL UNION ALL
SELECT 911, 'G' FROM DUAL;
Query 1:
SELECT e.name as employee,
w.name as related_to
FROM (
SELECT CONNECT_BY_ROOT( Employee ) AS employee,
boss AS related_to
FROM Rel
CONNECT BY PRIOR Boss = Employee
) r
INNER JOIN Workers e
ON e.id = r.employee
INNER JOIN Workers w
ON w.id = r.related_to
Results:
| EMPLOYEE | RELATED_TO |
|----------|------------|
| A | B |
| C | D |
| E | F |
| B | G |
| A | G |
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE Rel ( Employee, Boss ) AS
SELECT 100, 105 FROM DUAL UNION ALL
SELECT 177, 180 FROM DUAL UNION ALL
SELECT 105, 911 FROM DUAL UNION ALL
SELECT 400, 402 FROM DUAL;
CREATE TABLE Workers ( id, name ) AS
SELECT 100, 'A' FROM DUAL UNION ALL
SELECT 105, 'B' FROM DUAL UNION ALL
SELECT 177, 'C' FROM DUAL UNION ALL
SELECT 180, 'D' FROM DUAL UNION ALL
SELECT 400, 'E' FROM DUAL UNION ALL
SELECT 402, 'F' FROM DUAL UNION ALL
SELECT 911, 'G' FROM DUAL;
Query 1:
SELECT e.name as employee,
w.name as related_to
FROM (
SELECT CONNECT_BY_ROOT( Employee ) AS employee,
boss AS related_to
FROM Rel
CONNECT BY PRIOR Boss = Employee
) r
INNER JOIN Workers e
ON e.id = r.employee
INNER JOIN Workers w
ON w.id = r.related_to
Results:
| EMPLOYEE | RELATED_TO |
|----------|------------|
| A | B |
| C | D |
| E | F |
| B | G |
| A | G |
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
answered Nov 20 '18 at 0:05
MT0MT0
52k52756
52k52756
add a comment |
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%2f53382534%2fsql-transitive-relationships%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
Show the connect by attempt you made
– Caius Jard
Nov 19 '18 at 22:31