Error #1066 - Not unique table/alias in MySQL

Question

I am having problem in my SQL statement, I get the error

#1066 - Not unique table/alias: 'students'

Below is my code:

SELECT batches.*,students.*, courses.course_name AS course_name,
count(DISTINCT IF(attendances.student_id = students.id AND attendances.permitted = 0,attendances.id ,NULL)) AS absents_count,
count(DISTINCT IF(batches.id = students.batch_id ,students.id ,NULL)) AS students_count, count(DISTINCT IF(attendances.student_id = students.id AND attendances.permitted = 1,attendances.id ,NULL)) AS leaves_count 
FROM `batches`   
INNER JOIN courses ON courses.id = batches.course_id 
LEFT OUTER JOIN attendances ON attendances.batch_id = batches.id AND attendances.month_date = '2016-09-05'  
LEFT OUTER JOIN students ON students.id = attendances.student_id AND students.batch_id = batches.id 
INNER JOIN students ON  batches.id = students.batch_id 
WHERE ('2016-09-05' BETWEEN batches.start_date AND batches.end_date AND batches.is_active = 1 AND batches.is_deleted = 0 AND courses.is_deleted = 0 ) 
GROUP BY batches.id 
ORDER BY courses.course_name,batches.id 
LIMIT 0, 10;

leaves_count and absents_count works fine but when I add code for students_count it gives the mentioned error.

Answer 1

You are joining to a table students twice and in the select and where list it wouldn't know from which table it should take the data. You need to alias your table in at least one join, so for example replace:

LEFT OUTER JOIN students ON 
  students.id = attendances.student_id AND students.batch_id = batches.id 
INNER JOIN students ON  
  batches.id = students.batch_id 

With: (note I've replaced both of them just to show you the point)

LEFT JOIN students AS s1 ON 
  s1.id = attendances.student_id AND s1.batch_id = batches.id 
INNER JOIN students AS s2 ON  
  batches.id = s2.batch_id 

You also need to replace every column reference from students to either s1 or s2 in entire query to retrieve/constraint your query properly.

I've also removed the OUTER part in left join as it is superfluous.

Answer 2

use different aliasses for the table students and all related columns

select <Fields to be seleced>
FROM `batches`   
INNER JOIN courses ON courses.id = batches.course_id 
LEFT OUTER JOIN attendances ON attendances.batch_id = batches.id AND attendances.month_date = '2016-09-05'  
LEFT OUTER JOIN students st1 ON st1.id = attendances.student_id AND students.batch_id = batches.id 
INNER JOIN students st2 ON  batches.id = st2.batch_id 
WHERE ('2016-09-05' BETWEEN batches.start_date AND batches.end_date AND batches.is_active = 1 AND batches.is_deleted = 0 AND courses.is_deleted = 0 )  GROUP BY batches.id ORDER BY courses.course_name,batches.id LIMIT 0, 10;