SQLite：如何创建一个包含 3 个表和 2 个"端点"的内部连接？

Question

我理解内部连接的基本概念，但我在这种特殊情况下遇到了麻烦(以下三个表，PK = 主键，FK = 外键(-：

tbl_goal_context (many-to-many table)
rowId(PK)  |  goalRowId(FK)  |  contextRowId (FK)  |
1          |  2              |  5                  |

tbl_context_items
rowId(PK)  |  ...  |
5          |  ...  |

tbl_context_categories_items (many-to-many table)
rowId(PK)  |  catRowId(FK)  |  itemRowId(FK)  |
1          |  3             |  5              |

我正在尝试列出通过两个多对多表连接的tbl_context_items行，其中 catRowId 和 goalRowId 是已知的。

因此，例如，假设我想从连接到goalRowId = 2和catRowId = 3的tbl_context_items中提取所有行。

这是我的尝试(使用上面的示例编号(，它给了我错误： "错误：不明确的列名称：tbl_goal_context.goalRowId 无法执行语句">

SELECT tbl_context_categories_items.itemRowId, tbl_context_categories_items.catRowId, tbl_goal_context.goalRowId, tbl_goal_context.contextRowId, tbl_context_items.rowId AS rowId, tbl_context_items.shortText AS shortText, tbl_context_items.userMade AS userMade
FROM tbl_context_categories_items
INNER JOIN tbl_goal_context ON tbl_goal_context.contextRowId = tbl_context_items.rowId
INNER JOIN tbl_goal_context ON tbl_context_categories_items.itemRowId = tbl_context_items.rowId
WHERE tbl_context_categories_items.catRowId = 3
AND tbl_goal_context.goalRowId = 2
ORDER BY tbl_context_items.shortText ASC

内部连接在这里不完全合适吗？我需要子查询吗？

Answer 1

歧义是你tbl_goal_context两次加入(所以两组相同的列名，所以歧义(。

连接将更类似于：-

FROM tbl_context_categories_items
INNER JOIN tbl_context_items ON tbl_context_categories_items.itemRowid = tbl_context_items.rowid
INNER JOIN tbl_context_categories ON tbl_context_categories_items.catRowid
INNER JOIN tbl_goal_context ON tbl_context_items.rowid = tbl_goal_context.contextRowid
INNER JOIN tbl_goal_items ON tbl_goal_context.goalRowid = tbl_goal_items.rowid

这将根据关系图联接所有表(尽管查询不需要最后一个 JOIN，因为不包含tbl_goal_items中的任何列(。