1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| SELECT b.bookid,
b.title,
col.name AS collectionname,
a.gencod,
c.contributorid,
c.name AS contributorname,
c.firstname AS contributorfirstname,
f.functionid,
f.name AS functionname,
CASE
WHEN book.firstpublicationdate <> '' THEN DATE_FORMAT(STR_TO_DATE(CONCAT('1/',book.firstpublicationdate),'%d/%m/%Y'),'%d/%m/%Y')
ELSE ''
END AS firstpublicationdate,
book.monthpos,
book.yearpos
FROM book AS b
JOIN bookcontributor AS bc ON b.bookid = bc.bookid
JOIN contributor AS c ON c.contributorid = bc.contributorid
JOIN contract AS co ON co.contributorid = c.contributorid
JOIN contractedition AS ce ON co.contractid = ce.contractid
JOIN function AS f ON f.functionid = bc.functionid
JOIN article AS a ON b.bookid = a.bookid
JOIN collection AS col ON b.collectionid = col.collectionid
LEFT JOIN contracteditionbook AS ceb ON cceb.bookid = b.bookid
WHERE b.projecttype <> 'PLV'
AND b.projecttype <> 'ASSORT'
AND b.projecttype <> 'PACK'
AND b.houseid = 1
AND CASE
WHEN b.firstpublicationdate <> ''
THEN DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/', b.firstpublicationdate), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0
ELSE
DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/',b.monthpos,'/', b.yearpos), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0
END
AND ceb.editionid IS NULL |
Partager