CODEWITHSUNDEEP
mysqlsqlmysql-error-1248
Michael Lim
Michael Lim

Reputation: 13

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

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

Upvotes: 0

Views: 454

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753575

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

Upvotes: 1

Related Questions