CODEWITHSUNDEEP
sqloracle-database
tomaytotomato
tomaytotomato

Reputation: 4028

Pairing two records using Grandparent record? SQL

All,

I am trying to pair two or more records linked by a common Grandparent record.

At the time I run my query I will only know one of the records in the tuple / pair. Therefore I need to use this child record to navigate to the Grandparent record. From the grandparent record I will then need to find all other Grandchildren records.

Here is a rather crude drawing of the structure enter image description here

So far in my query I can find the Grandparent record but I do not know what to do from that point to find all other grand children

SELECT * FROM TABLE A
WHERE CREATED_DATE = sysdate
JOIN TABLE B
ON TABLE A.parent_row = TABLE B.row_id
JOIN TABLE C
//Grandparent table
ON TABLE B.parent_row = TABLE C.row_id

If there is a better way to do this? My only main filter in this query is that I am searching for records created today in Table A.

Thanks

Upvotes: 1

Views: 183

Answers (1)

Sebas
Sebas

Reputation: 21542

First, your query is invalid. The where clause comes after the joins:

SELECT * 
FROM TABLE A
    JOIN TABLE B ON TABLE A.parent_row = TABLE B.row_id
    JOIN TABLE C ON TABLE B.parent_row = TABLE C.row_id
WHERE CREATED_DATE = sysdate;

Secondly, you didn't filter by the grand-child you apparently have already. In this query, I use the YOUR_ID variable to represent it:

SELECT * 
FROM TABLE A
    JOIN TABLE B ON TABLE A.parent_row = TABLE B.row_id
    JOIN TABLE C ON TABLE B.parent_row = TABLE C.row_id
WHERE 
    CREATED_DATE = sysdate
AND TABLE A.row_id = YOUR_ID;

Thirdly, now that we retrieved the grand parents (table C.row_id), we may follow the same logic to join over again with table B (BB) and A (AA) to get all grand children:

SELECT TABLE AA.row_id  /* ids of all grand children. YOUR_ID should be included */
FROM TABLE A
    JOIN TABLE B ON TABLE A.parent_row = TABLE B.row_id
    JOIN TABLE C ON TABLE B.parent_row = TABLE C.row_id
    /* join over on table B then table A to get all grand children */
    JOIN TABLE BB ON TABLE BB.parent_row = TABLE C.row_id
    JOIN TABLE AA ON TABLE AA.parent_row = TABLE BB.row_id
WHERE 
    CREATED_DATE = sysdate
AND TABLE A.row_id = YOUR_ID;

Upvotes: 2

Related Questions