Search code examples
mysqlleft-joinalias

mysql how do you place an alias on a derived table

I have the following query

SELECT * FROM(
    (select * from `responses` a  order by id, exam, question)
       left JOIN
          (select max(exam) as maxexam from responses b )
              on a.exam <= b.maxexam
              and id > 2) c

I get the following error message

Every derived table must have its own alias
Solution

  • If you are using join condition on a.exam <= b.maxexam and id > 2 then uou need to give alias a and b to the two subqueries:

    SELECT * FROM
    /* alias a applied to first subquery: */
    (select * from `responses`  order by id, exam, question) a
    left JOIN
    /* alias b applied to second subquery: */
    (select max(exam) as maxexam from responses) b
    on a.exam <= b.maxexam and id > 2
;