Seryu Seryu - 6 months ago 17
MySQL Question

Every derived table has its own alias

i have the following query:

select userid, name, checktime, campaign,

CASE WHEN Hoursworked - 9 > 0 THEN Hoursworked - 9 ELSE 0 END Overtime
from
(

select
a.userid,a.name,a.campaign,

date(a.CHECKTIME),
timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as Hoursworked
from CHECKINOUT a

join CHECKINOUT b

on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid
where
a.CHECKTYPE = 'I'

AND b.CHECKTYPE = 'O'

group by date(a.CHECKTIME), userid
)


And it's giving this error

1248 - Every derived table must have its own alias



can you help me what wrong?

Answer

You need to add a name for the subquery that you have used.

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

So give your subquery an alias by:

select userid, name, checktime, campaign,

CASE WHEN Hoursworked - 9 > 0 THEN Hoursworked - 9 ELSE 0 END Overtime
from
(

    select  
    a.userid,a.name,a.campaign,

    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as Hoursworked
    from ...
) AS T  --here is the alias
Comments