CODEWITHSUNDEEP
mysqljoinambiguous
Index
Index

Reputation: 2381

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.

Upvotes: 0

Views: 1625

Answers (2)

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25445

You need to give your table names an alias, like below.

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

Not sure is the top lines right as i don't know you table structure so can't know which column comes from where.

Hopes this helps.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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)

Upvotes: 3

Related Questions