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.