Search code examples
mysqljoinambiguous

Ambiguous left join?

so I'm struggling with what I'm guessing is a very simple problem. I've searched around a bit, but none of the solutions I've found so far have worked on my problem.

SELECT arrangement_ID, hva, dato 
FROM tt_arrangement LEFT JOIN (tt_vaktliste_vakt)
ON (tt_arrangement.arrangement_ID = tt_vaktliste_vakt.arrangement_ID)

This naturally produces the "ambiguous error", since the column 'arrangement_ID' is present in both tt_arrangement and tt_vaktliste_vakt. Thinking this was easy to fix, I made the following changes:

SELECT **arrangement_ID.tt_arrangement**, hva, dato 
FROM tt_arrangement LEFT JOIN (tt_vaktliste_vakt)
ON (tt_arrangement.arrangement_ID = tt_vaktliste_vakt.arrangement_ID)

However, this produced the error "column doesn't exist". And that's where I'm stuck. Not sure if it matters, but when using SELECT * the query works as intended. Though that is not really an option for what I'm going to use the query for.

In advance, thanks for any replies.

Solution

  • Prefix the ambiguous column name with the tablename:

    SELECT t_arrangement.arrangement_ID, hva, dato  
FROM tt_arrangement LEFT JOIN (tt_vaktliste_vakt) 
ON (tt_arrangement.arrangement_ID = tt_vaktliste_vakt.arrangement_ID)

    (assumes hva, dato are unique column names)

    (You can also use aliases, but will still need to prefix ambiguous column names with alias)