-1

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?

John
  • 497
  • 2
  • 16
  • Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Please read & act on [mcve]. Please google error messages with tags & with & without your particular strings/names, this is a faq. – philipxy Oct 04 '18 at 22:13
  • The error message says a name is ambiguous. The ambiguity must be in the name before the dot and/or the name after the dot and/or the combination. You have two join arguments with the same table name but no aliases so they have the same default alias so using the alias/name is ambiguous. Learn about join aliases (aka correlation names). Learn how join works: It renames column c in table t aliased to a (which if not given defaults to t) to a.c then does a cross join then on & where remove rows not meeting the condition. – philipxy Oct 04 '18 at 22:28
  • @philipxy Are my edits what you mean by using text and not images? – John Oct 05 '18 at 17:56
  • Use text for anything expressible by text. ERD content is expressible by DDL. I guess you saw that, since you replaced your ERD image with example tables. But a [mcve] means cut & paste & runnable code. Give DDL. Give *code* formatted as a table to set input tables. Otherwise you expect everyone else to do that. Also give desired output. Also a clear description of how the output is a function of inputs. Talk of "connected" is vague. Finish/fix this: A row (itemRowId, ...) is in the output when a row (i.itemId, ...) is in tbl_context_items and ...". Or "... when context item itemRowId ...". – philipxy Oct 05 '18 at 20:42
  • Fix your ambiguity error. Then read my answer [How do I find relations between tables that are long-distance related?](https://stackoverflow.com/a/44124759/3404097) & its links. Then: Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. PS Almost certainly you don't want 2 tbl_goal_context joins here. – philipxy Oct 05 '18 at 20:52
  • Possible duplicate of [When to use SQL Table Alias](https://stackoverflow.com/q/198196/3404097) – philipxy Oct 05 '18 at 21:07

1 Answers1

2

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

  • Hi. The actual question asked is about the ambiguity error. Which you are not very clear about. And which is a duplicate & should be closed not answered. After they fix it they should post a new question about finding the right query. Then answer that question. – philipxy Oct 05 '18 at 20:51