While fetching data from 3 tables using join I get an error: Column 'semester' and 'department' in field list is ambiguous

user_15225788 0
Viewed 37

All three table contains 'semester' and 'department' column

SELECT DISTINCT 
  test_name,
  fname,
  lname,
  rno 
FROM exam_attempted_list
INNER JOIN stud_test ON
  exam_attempted_list.student_id = stud_test.student_id
INNER JOIN stud_reg ON
  exam_attempted_list.student_id = stud_reg.student_id WHERE semester='2nd' AND department='cse'
pdo sql
3 Answers

Use the following format, i.e, there were some statements which were repeating in your query, missing aliases in statements:

SELECT DISTINCT exam_attempted_list.test_name ,exam_attempted_list.fname, 
exam_attempted_list.lname , exam_attempted_list.rno
FROM exam_attempted_list
INNER JOIN stud_test 
ON exam_attempted_list.student_id = stud_test.student_id
INNER JOIN stud_reg 
ON exam_attempted_list.student_id = stud_reg.student_id 
WHERE semester='2nd' AND department='cse'
user_92665650

Use table aliase like exam_attempted_list.semester='2nd'

SELECT DISTINCT test_name ,fname, lname ,rno
FROM
exam_attempted_list 
INNER JOIN stud_test ON exam_attempted_list.student_id = stud_test.student_id
INNER JOIN stud_reg ON exam_attempted_list.student_id = stud_reg.student_id
WHERE exam_attempted_list.semester='2nd' AND exam_attempted_list.department='cse';
user_46021180 
SELECT DISTINCT eal.test_name , eal.fname, eal.lname , eal.rno 
FROM exam_attempted_list AS eal 
INNER JOIN stud_test AS st ON eal.student_id = st.student_id 
INNER JOIN stud_reg AS sr ON eal.student_id = sr.student_id 
WHERE eal.semester='2nd' AND eal.department='cse'

Replace eal with either st or sr depending on where you want the data from.

user_124373930
Related