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.
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;