How to get either a child or a parent record SQL

If I have a table that has an uniqueidentifier primary key called Id and there is a column called parentId, that points to another record in the same table that it may be linked to (column is null for parent records, child records have the Id of the parent record), how would I search and pull back a child record if one exists, and the parent record if there are no children? I am searching this table with data from another column that's not the primary key.

asked Jan 18 at 21:34

mpinales

143

2

You'll need to provide a better context for the data structure you're describing, as well as what you've tried as far as querying that's not working.

– MattD
Jan 18 at 21:37

what have you tried so far? where do you get stuck?

– pastacool
Jan 18 at 21:37

stackoverflow.com/tags/sql/info

– Tony
Jan 18 at 21:58

add a comment |

asked Jan 18 at 21:34

mpinales

143

2

You'll need to provide a better context for the data structure you're describing, as well as what you've tried as far as querying that's not working.

– MattD
Jan 18 at 21:37

what have you tried so far? where do you get stuck?

– pastacool
Jan 18 at 21:37

stackoverflow.com/tags/sql/info

– Tony
Jan 18 at 21:58

add a comment |

asked Jan 18 at 21:34

mpinales

143

sql

asked Jan 18 at 21:34

mpinales

143

asked Jan 18 at 21:34

mpinales

143

asked Jan 18 at 21:34

mpinales

143

asked Jan 18 at 21:34

mpinales

143

asked Jan 18 at 21:34

mpinales

143

2

You'll need to provide a better context for the data structure you're describing, as well as what you've tried as far as querying that's not working.

– MattD
Jan 18 at 21:37

what have you tried so far? where do you get stuck?

– pastacool
Jan 18 at 21:37

stackoverflow.com/tags/sql/info

– Tony
Jan 18 at 21:58

add a comment |

2

You'll need to provide a better context for the data structure you're describing, as well as what you've tried as far as querying that's not working.

– MattD
Jan 18 at 21:37

what have you tried so far? where do you get stuck?

– pastacool
Jan 18 at 21:37

stackoverflow.com/tags/sql/info

– Tony
Jan 18 at 21:58

You'll need to provide a better context for the data structure you're describing, as well as what you've tried as far as querying that's not working.

– MattD
Jan 18 at 21:37

what have you tried so far? where do you get stuck?

– pastacool
Jan 18 at 21:37

stackoverflow.com/tags/sql/info

– Tony
Jan 18 at 21:58

add a comment |

1 Answer
1

active

oldest

votes

I believe what you are looking for is Connect By Level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

I took the code from the above link and put it below. I hate dead links so this will keep the answer alive here on Stackoverflow.

I don't like providing examples that utilize DDL's as not everyone has the database privileges to create tables etc... The example below works with just plain SQL (Only uses DML). I like using With Blocks to form my tables in easy examples like this one (aka Common Table Expression - CTE).

WITH sample_data AS

(

    SELECT 'KING'   AS ENAMe, 7839 AS EMPNO, NULL AS MGR FROM DUAL UNION ALL

    SELECT 'JONES'  AS ENAMe, 7566 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'SCOTT'  AS ENAMe, 7788 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'ADAMS'  AS ENAMe, 7876 AS EMPNO, 7788 AS MGR FROM DUAL UNION ALL

    SELECT 'FORD'   AS ENAMe, 7902 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'SMITH'  AS ENAMe, 7369 AS EMPNO, 7902 AS MGR FROM DUAL UNION ALL

    SELECT 'BLAKE'  AS ENAMe, 7698 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'ALLEN'  AS ENAMe, 7499 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'WARD'   AS ENAMe, 7521 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'MARTIN' AS ENAMe, 7654 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'TURNER' AS ENAMe, 7844 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'JAMES'  AS ENAMe, 7900 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'CLARK'  AS ENAMe, 7782 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'MILLER' AS ENAMe, 7934 AS EMPNO, 7782 AS MGR FROM DUAL

)



SELECT lpad(' ',LEVEL*2,' ')||ename ename, empno, mgr

FROM sample_data

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

;

Screenshot below to show it in action.

enter image description here

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54261711%2fhow-to-get-either-a-child-or-a-parent-record-sql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

I believe what you are looking for is Connect By Level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

I took the code from the above link and put it below. I hate dead links so this will keep the answer alive here on Stackoverflow.

WITH sample_data AS

(

    SELECT 'KING'   AS ENAMe, 7839 AS EMPNO, NULL AS MGR FROM DUAL UNION ALL

    SELECT 'JONES'  AS ENAMe, 7566 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'SCOTT'  AS ENAMe, 7788 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'ADAMS'  AS ENAMe, 7876 AS EMPNO, 7788 AS MGR FROM DUAL UNION ALL

    SELECT 'FORD'   AS ENAMe, 7902 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'SMITH'  AS ENAMe, 7369 AS EMPNO, 7902 AS MGR FROM DUAL UNION ALL

    SELECT 'BLAKE'  AS ENAMe, 7698 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'ALLEN'  AS ENAMe, 7499 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'WARD'   AS ENAMe, 7521 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'MARTIN' AS ENAMe, 7654 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'TURNER' AS ENAMe, 7844 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'JAMES'  AS ENAMe, 7900 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'CLARK'  AS ENAMe, 7782 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'MILLER' AS ENAMe, 7934 AS EMPNO, 7782 AS MGR FROM DUAL

)



SELECT lpad(' ',LEVEL*2,' ')||ename ename, empno, mgr

FROM sample_data

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

;

Screenshot below to show it in action.

enter image description here

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

add a comment |

I believe what you are looking for is Connect By Level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

I took the code from the above link and put it below. I hate dead links so this will keep the answer alive here on Stackoverflow.

WITH sample_data AS

(

    SELECT 'KING'   AS ENAMe, 7839 AS EMPNO, NULL AS MGR FROM DUAL UNION ALL

    SELECT 'JONES'  AS ENAMe, 7566 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'SCOTT'  AS ENAMe, 7788 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'ADAMS'  AS ENAMe, 7876 AS EMPNO, 7788 AS MGR FROM DUAL UNION ALL

    SELECT 'FORD'   AS ENAMe, 7902 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'SMITH'  AS ENAMe, 7369 AS EMPNO, 7902 AS MGR FROM DUAL UNION ALL

    SELECT 'BLAKE'  AS ENAMe, 7698 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'ALLEN'  AS ENAMe, 7499 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'WARD'   AS ENAMe, 7521 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'MARTIN' AS ENAMe, 7654 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'TURNER' AS ENAMe, 7844 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'JAMES'  AS ENAMe, 7900 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'CLARK'  AS ENAMe, 7782 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'MILLER' AS ENAMe, 7934 AS EMPNO, 7782 AS MGR FROM DUAL

)



SELECT lpad(' ',LEVEL*2,' ')||ename ename, empno, mgr

FROM sample_data

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

;

Screenshot below to show it in action.

enter image description here

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

add a comment |

I believe what you are looking for is Connect By Level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

I took the code from the above link and put it below. I hate dead links so this will keep the answer alive here on Stackoverflow.

WITH sample_data AS

(

    SELECT 'KING'   AS ENAMe, 7839 AS EMPNO, NULL AS MGR FROM DUAL UNION ALL

    SELECT 'JONES'  AS ENAMe, 7566 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'SCOTT'  AS ENAMe, 7788 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'ADAMS'  AS ENAMe, 7876 AS EMPNO, 7788 AS MGR FROM DUAL UNION ALL

    SELECT 'FORD'   AS ENAMe, 7902 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'SMITH'  AS ENAMe, 7369 AS EMPNO, 7902 AS MGR FROM DUAL UNION ALL

    SELECT 'BLAKE'  AS ENAMe, 7698 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'ALLEN'  AS ENAMe, 7499 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'WARD'   AS ENAMe, 7521 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'MARTIN' AS ENAMe, 7654 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'TURNER' AS ENAMe, 7844 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'JAMES'  AS ENAMe, 7900 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'CLARK'  AS ENAMe, 7782 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'MILLER' AS ENAMe, 7934 AS EMPNO, 7782 AS MGR FROM DUAL

)



SELECT lpad(' ',LEVEL*2,' ')||ename ename, empno, mgr

FROM sample_data

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

;

Screenshot below to show it in action.

enter image description here

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

I believe what you are looking for is Connect By Level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

I took the code from the above link and put it below. I hate dead links so this will keep the answer alive here on Stackoverflow.

WITH sample_data AS

(

    SELECT 'KING'   AS ENAMe, 7839 AS EMPNO, NULL AS MGR FROM DUAL UNION ALL

    SELECT 'JONES'  AS ENAMe, 7566 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'SCOTT'  AS ENAMe, 7788 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'ADAMS'  AS ENAMe, 7876 AS EMPNO, 7788 AS MGR FROM DUAL UNION ALL

    SELECT 'FORD'   AS ENAMe, 7902 AS EMPNO, 7566 AS MGR FROM DUAL UNION ALL

    SELECT 'SMITH'  AS ENAMe, 7369 AS EMPNO, 7902 AS MGR FROM DUAL UNION ALL

    SELECT 'BLAKE'  AS ENAMe, 7698 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'ALLEN'  AS ENAMe, 7499 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'WARD'   AS ENAMe, 7521 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'MARTIN' AS ENAMe, 7654 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'TURNER' AS ENAMe, 7844 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'JAMES'  AS ENAMe, 7900 AS EMPNO, 7698 AS MGR FROM DUAL UNION ALL

    SELECT 'CLARK'  AS ENAMe, 7782 AS EMPNO, 7839 AS MGR FROM DUAL UNION ALL

    SELECT 'MILLER' AS ENAMe, 7934 AS EMPNO, 7782 AS MGR FROM DUAL

)



SELECT lpad(' ',LEVEL*2,' ')||ename ename, empno, mgr

FROM sample_data

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR

;

Screenshot below to show it in action.

enter image description here

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

edited Jan 18 at 23:18

answered Jan 18 at 22:55

Code Novice

19813

answered Jan 18 at 22:55

Code Novice

19813

answered Jan 18 at 22:55

Code Novice

19813

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Brtdku