Search code examples
sqliteinner-join

SQLite: How to create an INNER JOIN that includes 3 tables and 2 'end points'?

I understand the basic concept of INNER JOINs but I'm having trouble with this particular case (the following three tables, PK = Primary Key, FK = Foreign Key)-:

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              |

I'm trying to list rows from tbl_context_items that are connected via the two many-to-many tables where catRowId and goalRowId are known.

So, for example, let's say I want to pull all the rows from tbl_context_items that are connected to goalRowId = 2 and catRowId = 3.

Here is my attempt (using the example numbers above), which is giving me the error: "Error: ambiguous column name: tbl_goal_context.goalRowId Unable to execute statement"

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

Are INNER JOINs not fully appropriate here? Do I need a sub-query?

Solution

  • The ambiguity is that you are joining tbl_goal_context twice (so 2 sets of identical column names, and so the ambiguity).

    The joins would be more along the lines of :-

    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

    This joins all of the tables according to the diagram (although the last JOIN isn't required for the query as you don't include any columns from tbl_goal_items).