SQL JOIN cant get it working

Question

I know the forum is full of this questions but i cant find the solution. I want to join the table users from user_to_designment they both have a column with user_id the error that i get is:

select user_id, designment_id FROM user_to_designment
FULL JOIN users
ON  user_to_designment.user_id = users.user_id


 LIMIT 0, 25 
MySQL meldt: Documentatie

#1052 - Column 'user_id' in field list is ambiguous

I use this query:

select user_id, designment_id FROM user_to_designment
FULL JOIN users
ON  user_to_designment.user_id = users.user_id

Please some advice

What do you think the words in "Column 'user_id' in field list is ambiguous " mean? — TZHX, Jun 03 '16 at 08:25
Possible duplicate of [1052: Column 'id' in field list is ambiguous](http://stackoverflow.com/questions/6638520/1052-column-id-in-field-list-is-ambiguous) — Jocelyn, Jun 03 '16 at 14:09

score 1 · Answer 1 · answered Jun 03 '16 at 08:22

In your select list prefix the user_id with the table name:

select users.user_id, designment_id FROM user_to_designment
FULL JOIN users
ON  user_to_designment.user_id = users.user_id

Both columns have user_id, SQL cannot choose between them, you must specify explicitly.

score 0 · Answer 2 · answered Jun 03 '16 at 08:23

0

You need to specify which user_id to return in your select, eg select users.user_id or Select user_to_designment.user_id

answered Jun 03 '16 at 08:23

OTTA

1,071
7
8

score 0 · Answer 3 · answered Jun 03 '16 at 08:23

0

You must clarify which user_id table column you are selecting.

select a.user_id,b.designment_id from user_to_designment b
full join users a
on b.user_id=a.user_id

answered Jun 03 '16 at 08:23

kostas

461
5
13

score 0 · Answer 4 · answered Jun 03 '16 at 08:24

0

This is because "user_id" field exists on both tables. you must put table name before field name.

    select user_to_designment.user_id, designment_id FROM user_to_designment
FULL JOIN users
ON  user_to_designment.user_id = users.user_id

answered Jun 03 '16 at 08:24

Toni Cordon Batlle

16
1

score 0 · Answer 5 · answered Jun 03 '16 at 08:26

It seems user_id exist in both the tables. so it gives the ambiguous error. We should say from which table we have to pick the column.

SELECT users.user_id
    ,user_to_designment.designment_id
FROM user_to_designment
FULL JOIN users ON user_to_designment.user_id = users.user_id

You may use either users.user_id or user_to_designment.user_id in your select statement. Always use tablename.columnname format. It avoids confusion.

SQL JOIN cant get it working

5 Answers5