Skip to content
Advertisement

Why am I getting a duplicated column when doing this SQL query?

I am trying to do a set of queries in mySQL. In my final query I am trying to select the course_id column, but there are two columns with the title course_id and the exact same values, so I am encountering an error saying

Query Error: Error: ER_NON_UNIQ_ERROR: Column ‘course_id’ in field list is ambiguous

and I am not sure how I can resolve it. All I want is for it to select one of the columns called course_id (however, I would assume this is not best practice)

Here is the link to my db-fiddle and here are the queries I am making

SELECT ID, course_id FROM teaches;

SELECT * FROM classroom WHERE capacity > 50;

SELECT * FROM section;

SELECT DISTINCT course_id FROM (SELECT * FROM classroom WHERE capacity > 50) AS c INNER JOIN section AS s ON c.room_no = s.room_no;


SELECT * FROM teaches AS i INNER JOIN (SELECT DISTINCT course_id FROM (SELECT * FROM classroom WHERE capacity > 50) AS c INNER JOIN section AS s ON c.room_no = s.room_no) AS j ON i.course_id = j.course_id;

-- SELECT course_id FROM teaches AS i INNER JOIN (SELECT DISTINCT course_id FROM (SELECT building, room_no, capacity FROM classroom WHERE capacity > 50) AS c INNER JOIN section AS s ON c.room_no = s.room_no) AS j ON i.course_id = j.course_id;

Advertisement

Answer

you need to select from wich table you want course_id from, be it i.course_id or j.course_id.

SELECT i.course_id FROM teaches AS i INNER JOIN (SELECT DISTINCT course_id FROM (SELECT building, room_no, capacity FROM classroom WHERE capacity > 50) AS c INNER JOIN section AS s ON c.room_no = s.room_no) AS j ON i.course_id = j.course_id;

as it is in the inner join, either of them will show the same data.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement