"Every derived table must have its own alias" Error in SQL

Question

I have a following SQL query and I'm getting an error "Every derived table must have its own alias.could any one please help me to solve this?

SELECT
    c.clientID_PK,
    c.clientName,
    d1.draftCount,
    d2.purchaseOrderValue,
    d2.averageValue
FROM client c
LEFT JOIN
    (select
        COUNT(DISTINCT d.draftID_PK)  as draftCount
     from draft d
     where d.draftDate between NOW() - INTERVAL 90 DAY and NOW())
    )d1 ON TRUE
LEFT JOIN
    (
     SELECT
        ROUND(sum(p.total_finalValue),2) as purchaseOrderValue
         ROUND((p.poValue / 12),2) as averageValue
     FROM paymentengine_data p
     WHERE p.poDate between NOW() - INTERVAL 90 DAY and NOW()
    )d2 ON TRUE
 WHERE c.typeID_FK = 1 AND c.stateID_FK = 2 AND c.statusID_FK = 2
       AND d1.clientID_FK = c.clientID_PK  AND d2.purchaserID_FK = c.clientID_PK
 GROUP BY c.clientID_PK

possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) — , Mar 31 '14 at 01:36
The error message is same as in the 'possible duplicate' question, but the problem here is the extra close parenthesis as diagnosed by Ravinder. The code here attempts to supply aliases, but the extra parenthesis confuses the parser, which could also complain about the unexpected close parenthesis. That really means this question should be closed as 'off-topic' because it is a 'trivial typo' that isn't going to be useful to other people in the future. — Jonathan Leffler, Mar 31 '14 at 01:53

score 2 · Answer 1 · answered Mar 31 '14 at 01:44

Your first LEFT JOIN has an extra closing brace and that is causing the error.

Change:

LEFT JOIN
    (select
        COUNT(DISTINCT d.draftID_PK)  as draftCount
     from draft d
     where d.draftDate between NOW() - INTERVAL 90 DAY and NOW()) -- <-- xtra )
    )d1 ON TRUE

To:

LEFT JOIN
    (select
        COUNT(DISTINCT d.draftID_PK)  as draftCount
     from draft d
     where d.draftDate between NOW() - INTERVAL 90 DAY and NOW()
    )d1 ON TRUE

score 0 · Answer 2 · answered Mar 31 '14 at 02:06

Here is a query that might actually work:

SELECT c.clientID_PK, c.clientName,
       d1.draftCount, d2.purchaseOrderValue, d2.averageValue
FROM client c left join
     (select clientID_FK, COUNT(DISTINCT d.draftID_PK)as draftCount
      from draft d
      where d.draftDate between NOW() - INTERVAL 90 DAY and NOW()
      group by clientID_FK
     ) d1 
     on d1.clientID_FK = c.clientID_PK LEFT JOIN
     (SELECT purchaserID_FK, ROUND(sum(p.total_finalValue),2) as purchaseOrderValue
             ROUND((p.poValue / 12),2) as averageValue
      FROM paymentengine_data p
      WHERE p.poDate between NOW() - INTERVAL 90 DAY and NOW()
      GROUP BY purchaserID_FK
     ) d2
     ON d2.purchaserID_FK = c.clientID_PK
WHERE c.typeID_FK = 1 AND c.stateID_FK = 2 AND c.statusID_FK = 2;

Here are some additional changes (besides the extra closing paren):

The first subquery groups by clientID_FK and includes the column in the select.
The on condition has been moved from the where to the on.
The second subquery groups by purchaserID_FK and includes the column in the select.
The on condition has been moved from the where clause to the on.
The outer group by has been removed. Presumably, the primary key is unique on the client table.

"Every derived table must have its own alias" Error in SQL

2 Answers2