从子表到子表再到父表的 SQL 查询 [英] SQL Query from getting SubChild to Child to Parent Tables

问题描述

美好的一天，

我正在尝试为我的场景创建自定义查询.这是我创建的 DEMO.

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

假设我有 2 个或更多父表，而这个表将被一个子表使用.

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

然后有一个子表，它使用这 3 个父表.

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           

和子表的孩子

tblChildOfChild

tblChildOfChild

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

到目前为止我尝试的是在 tblChildOfChild 和 tblChild 之间使用 left join 但我不知道如何访问tblChild 的 3 个父母.我需要的是创建一个在 tblChildOfChild 中有条件的查询.到目前为止，我所做的如下所示.

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.

预期结果应该是:

| 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.

推荐答案

我认为 OP 唯一做错的就是 join 错误的键.

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

无论如何先试试这个.

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

这篇关于从子表到子表再到父表的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！