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 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