Not unique table/alias (42000)

Question

I get the error ERROR 1066 (42000): Not unique table/alias: 'STUDENT_TBL'

select STUDENT,DATE,MARK from Assessments inner join STUDENT_TBL on Assessments,ID_STUDENT=STUDENT_TBL,STD_ID inner join Visit_log,ID_STUDENT=STUDENT_TBL,STD_ID where STUDENT_TBL,STD_ID IN (select ID_STUDENT FROM Assessments group by ID_STUDENT having avg(MARK)>3.7);

How to fix it ?

Answer 1

You should use '.' instead of ',' to access Table_Name.column_name.

select STUDENT,DATE,MARK 
from Assessments AS Assessments 
inner join STUDENT_TBL AS STUDENT_TBL on Assessments.ID_STUDENT=STUDENT_TBL.STD_ID 
inner join Visit_log AS Visit_log on Visit_log.ID_STUDENT=STUDENT_TBL.STD_ID 
where STUDENT_TBL.STD_ID IN (select ID_STUDENT FROM Assessments 
                             group by ID_STUDENT having avg(MARK)>3.7

Also use alias like Assessments.ID_STUDENT while selecting columns in select clause if those are present in more than one table

Answer 2

If you use schema and tablename you must seperate it with dot like this

select STUDENT, Assessments .`DATE`,MARK from Assessments
inner join STUDENT_TBL on Assessments.ID_STUDENT=STUDENT_TBL.STD_ID
inner join Visit_log.ID_STUDENT=STUDENT_TBL.STD_ID
where STUDENT_TBL.STD_ID IN (select ID_STUDENT FROM Assessments group by ID_STUDENT having avg(MARK)>3.7);