MySQL Every derived table must have its own alias

Question

Hi I need to generate an sql query by joining two queries

1st Query : Getting all students data

SELECT * FROM students where class = 1 and section = 'A'

2nd Query : Getting the count of each attendance

SELECT  roll_no,full_name, 

SUM(hasAttended= 'P') AS DaysPresent, 

SUM(hasAttended= 'A') AS DaysAbsent, 

COUNT(*) AS totalClasses

FROM     attendance

GROUP BY roll_no

NOW I need to join the two tables and produce a resultant table

I am trying the following query but getting an error :

1248 - Every derived table must have its own alias

My Query is as follows :

SELECT * FROM students as st

INNER JOIN 

(SELECT  att.roll_no,att.full_name, 

SUM(att.hasAttended= 'P') AS DaysPresent, 

SUM(att.hasAttended= 'A') AS DaysAbsent, 

COUNT(*) AS totalClasses

FROM     attendance as att

GROUP BY att.roll_no)

ON st.roll_no = att.roll_no

ORDER BY  st.roll_no

Can anybody please solve the above error

Answer 1

MySQL requires that all derived tables and subqueries have an alias. You are missing an alias at the end of the closing parentheses for the subqquery:

SELECT * 
FROM students as st
INNER JOIN 
(
  SELECT  att.roll_no,att.full_name, 
    SUM(att.hasAttended= 'P') AS DaysPresent, 
    SUM(att.hasAttended= 'A') AS DaysAbsent, 
    COUNT(*) AS totalClasses
  FROM     attendance as att
  GROUP BY att.roll_no
) att  ---------------------------< this is missing
  ON st.roll_no = att.roll_no
WHERE st.class = 1 
ORDER BY  st.roll_no