Getting Error - Every derived table must have its own alias error

Question

The query is to get the dates for each table, I have Checked my code but it still error's "Every derived table must have its own alias error"


SELECT T.* 
    FROM (
     SELECT DISTINCT(DATE_FORMAT(p.deliverDate, '%M')) as PDate
     FROM piglet_po p 
     WHERE p.raiserID = '0025' AND p.rotationNo = '3' 

        AND p.deliverDate NOT IN 
        ( SELECT DISTINCT(DATE_FORMAT(f.issuedDate, '%M')) as FDate
           FROM feed_slip f             
           WHERE f.raiserID = '0025' AND f.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'                

           AND f.issuedDate NOT IN 
           ( SELECT DISTINCT(DATE_FORMAT(v.issuedDate, '%M')) as VDate
              FROM vet_slip
              WHERE v.raiserID = '0025' AND v.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'

            AND v.issuedDate NOT IN 
             ( SELECT DISTINCT(DATE_FORMAT(w.issuedDate, '%M')) as WDate
                FROM weight_slip w  
                WHERE w.raiserID = '0025' AND w.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'                                     
              ) 
          ) 

      ) T

You can see from the formatting that you're forgetting a quotation mark after one of the dates, which is possibly what's screwing it up. — Zane Bien, Jul 16 '12 at 01:33
possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) — OMG Ponies, Jul 16 '12 at 01:33
@ZaneBien Thank you for noticing.. I've checked on my system code there's no missing quotation, I've edited my post.. — Michael Lim, Jul 16 '12 at 01:42

score 1 · Accepted Answer · edited May 05 '14 at 04:34

You forgot the alias v in:

        SELECT DISTINCT(DATE_FORMAT(v.issuedDate, '%M')) as VDate
          FROM vet_slip
         WHERE v.raiserID = '0025' AND 
               v.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'

There was also a close parenthesis missing, AFAICT. If you simply dropped the last one, then the alias T was in the wrong place. I've assumed that it was one of the 'inner parentheses' that was dropped.

The corrected query is, therefore:

SELECT T.* 
  FROM (SELECT DISTINCT(DATE_FORMAT(p.deliverDate, '%M')) as PDate
          FROM piglet_po p 
         WHERE p.raiserID = '0025' AND p.rotationNo = '3'
           AND p.deliverDate NOT IN 
               (SELECT DISTINCT(DATE_FORMAT(f.issuedDate, '%M')) as FDate
                  FROM feed_slip f             
                 WHERE f.raiserID = '0025'
                   AND f.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'
                   AND f.issuedDate NOT IN 
                       (SELECT DISTINCT(DATE_FORMAT(v.issuedDate, '%M')) as VDate
                          FROM vet_slip v
                         WHERE v.raiserID = '0025'
                           AND v.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'
                           AND v.issuedDate NOT IN
                               (SELECT DISTINCT(DATE_FORMAT(w.issuedDate, '%M')) as WDate
                                  FROM weight_slip w
                                 WHERE w.raiserID = '0025'
                                   AND w.issuedDate BETWEEN '2012-06-01' AND '2012-11-01'
                               ) 
                       ) 
               )
       ) T

It only returns the PDate.. It needs to return all the days from FDate,VDate and WDate that has transactions recorded by raiser 0025 within the set issuedDate — Michael Lim, Jul 16 '12 at 02:50
That's a very different issue - and a wholly separate question. I've fixed the syntax, I think, but it is up to you to fix the logic of the query. I don't understand either what you are seeking or why you have the deeply nested logic you have. I'm not all that interested in it, either. You asked 'why are you getting a particular syntax error', and there were two identifiable problems that could have led to you seeing it, both of which are fixed in the revised query. — Jonathan Leffler, Jul 16 '12 at 02:52

Getting Error - Every derived table must have its own alias error

1 Answers1