ERROR 1052 (23000): Column 'course_id' in where clause is ambiguous

Question

I'm new to MySQL so please tell me if my question is missing information,

I have a query that works fine:

select au.email, sm.created, sm.grade, sm.max_grade
from auth_user au, courseware_studentmodule sm
where sm.student_id = au.id
and course_id = 'MyCourse'
and sm.module_type = 'problem';

But when I want to add another column from a different table:

select au.email, sm.created, sce.created , sm.grade, sm.max_grade
from auth_user au, courseware_studentmodule sm,  student_courseenrollment sce
where sm.student_id = au.id and sm.student_id = sce.id
and course_id = 'MyCourse'
and sm.module_type = 'problem';

I get this error

ERROR 1052 (23000): Column 'course_id' in where clause is ambiguous

Anyone know why?

Thanks

possible duplicate of [How to fix an "ambigous column name error using inner join" error](http://stackoverflow.com/questions/17029096/how-to-fix-an-ambigous-column-name-error-using-inner-join-error) — Ben, Aug 19 '13 at 20:36

Lorenz Meyer · Accepted Answer · 2020-06-12T13:03:54.247

7

This is because the column course_id is present in more than two tables.

Write sm.course_id or sce.course_id and it will work.

edited Jun 12 '20 at 13:03

answered Aug 19 '13 at 20:41

Lorenz Meyer

19,166
22
75
121

Awesome thanks, QUESTION: since course_id only exists in sm, how come the first query worked [when I only had two tables]? – user2333346 Aug 19 '13 at 20:49
I am sure its only in sm – user2333346 Aug 19 '13 at 20:50

score 3 · Answer 2 · answered Aug 19 '13 at 20:35

3

You are joining multiple tables, at least two of the tables have the column course_id. In your statement and course_id = 'MyCourse' you are not specifying which table has course_id.

answered Aug 19 '13 at 20:35

datasage

19,153
2
48
54

I did check that actually and only courseware_studentmodule sm has the course_id column, and when I switch the above to sm.course_id = 'MyCourse' I just get the same error, I know its strange how the first one works! – user2333346 Aug 19 '13 at 20:39

score 2 · Answer 3 · answered Aug 19 '13 at 20:35

2

student_courseenrollment and one of your other tables both have a column called course_id. Use a table alias, e.g. au.course_id.

answered Aug 19 '13 at 20:35

Ben

51,770
36
127
149

Miguelo · Answer 4 · 2013-08-19T20:42:15.027

2

you need to use the alias of the table with the column of course id in it

like sce.course_id

as stated in the comment below this might change your result so use the table name of the table of that is used in the where clause or the alias of that table

edited Aug 19 '13 at 20:42

answered Aug 19 '13 at 20:35

Miguelo

1,078
6
13

1

This is almost certainly incorrect; the restriction in the WHERE clause was there before this table was added. As it's not part of the JOIN you may be changing the results. – Ben Aug 19 '13 at 20:39
But if you look at the first query on the top with only au and sm, the course_id column only exists in sm, but he query works! – user2333346 Aug 19 '13 at 20:57

score 0 · Answer 5 · edited Mar 06 '15 at 09:38

0

You are using two different tables with same column name. If you want to use that column name in any query, you should use with table name Eg:

select * from table1,table2 where table1.userId='any id';

edited Mar 06 '15 at 09:38

Robert

5,278
43
65
115

answered Mar 06 '15 at 09:34

user4640267

1

ERROR 1052 (23000): Column 'course_id' in where clause is ambiguous

5 Answers5