SQL Query from getting SubChild to Child to Parent Tables

Good day,

I’m trying to create a custom query for my scenario. Here’s the DEMO I created.

Suppose I have 2 or more parent tables and this table will be consume by a child table.

tblParent1

id | name  | age
1  | Lima  | 35
2  | Jenny | 45

tblParent2

id | name  | age
1  | John | 42
2  | Tess | 41

tblParent3

id | name  | age
1  | Jeff  | 39
2  | Gyro  | 47

Then there’s a child table where it consumes these 3 parent tables.

tblChild

id | note | tblParent1_key | tblParent2_key | tblParent3_key 
1  | ...  | 1              | 2              | 1           
2  | ...  | 2              | 1              | 1           

And the child of child table

tblChildOfChild

| id | feedback | tblChild_key |
| 1  | anything | 1

What I tried so far is using the left join between tblChildOfChild and tblChild but I don’t have an idea how can I access the 3 parents of the tblChild. What I need is create a query that has condition in tblChildOfChild. What I’ve done so far is shown below.

SELECT t.id, t.feedback, t1.note as note, p1.name as parent1, p2.name as parent2, 
p3.name as parent3
FROM tblChildOfChild t 
LEFT JOIN tblChild t1 ON t.tblChild_key = t1.id
LEFT JOIN tblParent1 p1 ON t1.id = p1.id
LEFT JOIN tblParent2 p2 ON p1.id = p2.id
LEFT JOIN tblParent3 p3 ON p2.id = p3.id
WHERE t.id = 1

But I’m getting a different result from what I expected.

The expected result should be:

| id | feedback | note | parent1 | parent2 | parent3 |
| 1  | anythin  | ...  | Lima    | Tess    | Jeff    |

I’m stuck with this for hours. I hope somebody can help me or give some new knowledge.

Answer

I think the only thing OP do wrong is join the wrong key.

anyway try this first.

SELECT t.id, t.feedback, t1.note as note, p1.name as parent1, p2.name as parent2, 
p3.name as parent3
FROM tblChildOfChild t 
LEFT JOIN tblChild t1 ON t.tblChild_key = t1.id
LEFT JOIN tblParent1 p1 ON t1.tblParent1_key = p1.id
LEFT JOIN tblParent2 p2 ON t1.tblParent2_key = p2.id
LEFT JOIN tblParent3 p3 ON t1.tblParent3_key = p3.id
WHERE t.id = 1