0

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 ?

Artem Matrosov
  • 3
  • 1

2 Answers2

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

Rohan
  • 3,068
  • 1
  • 20
  • 26
0

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);
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39