SELECT
    CAST(MD5(CONCAT('collection-', collections.corpus, '-', collections.ident)) AS CHAR (32)) AS guid,
    collections.*
FROM (
    SELECT
        CAST(MD5(CONCAT('collection-', coll_editoriales.ident)) AS CHAR (32)) AS guid_interne,
        coll_editoriales.ident,
        trim(coll_editoriales.libelle) AS nom_collection,
		IF(
			coll_editoriales.marq_ident IN (select ident FROM marques WHERE marques.edit_ident IN (1, 21))
			OR
			(coll_editoriales.edit_ident IN (1) AND coll_editoriales.marq_ident IS NULL),
			CASE
				WHEN coll_editoriales.edit_ident IN(1, 21) AND coll_editoriales.marq_ident IN ('01V','01Q','01B03','01R','01P','01O','21B') THEN 'flammarion'
				WHEN coll_editoriales.edit_ident IN(1) AND coll_editoriales.marq_ident IS NULL AND coll_editoriales.marq_fictive_ident IS NULL THEN 'flammarion'
				WHEN coll_editoriales.marq_ident IN('01N') THEN 'arthaud'
				WHEN coll_editoriales.marq_ident IN('01C') THEN 'pygmalion'
				WHEN coll_editoriales.marq_ident IN('01E-01D') THEN 'jeunesse'
			END,
			null
		) as corpus,
        GROUP_CONCAT(IF(doc_web.document_officiel = 1, CONCAT(doc_web.document_libelle, '§', doc_web.document_emplacement),NULL) SEPARATOR '!') AS sites_web_officiels,
        GROUP_CONCAT(IF(doc_web.document_officiel = 0, CONCAT(doc_web.document_libelle, '§', doc_web.document_emplacement),NULL) SEPARATOR '!') AS sites_web_non_officiels

    FROM coll_editoriales
    LEFT JOIN
    (
		SELECT
			documents.libelle as document_libelle,
            documents.emplacement as document_emplacement,
            documents.cole_ident as document_cole_ident,
            documents.officiel as document_officiel
        FROM (SELECT * FROM documents WHERE type_docu = 9) as documents
        LEFT JOIN type_docus ON type_docus.code = documents.type_docu
    )AS doc_web
    ON doc_web.document_cole_ident = coll_editoriales.ident
	RIGHT OUTER JOIN (
        SELECT
            DISTINCT(cole_ident)
        FROM produits
        WHERE
			produits.iscommercialisable = 1
    ) as produits
    ON produits.cole_ident = coll_editoriales.ident
    WHERE
        coll_editoriales.edit_ident IN(1, 21)
        AND (
          coll_editoriales.marq_ident IN('01V','01Q','01B03','01R','01P','01O','21B','01N','01C','01E-01D') OR (coll_editoriales.marq_ident IS NULL AND coll_editoriales.edit_ident != 21)
        )
        AND coll_editoriales.marq_fictive_ident IS NULL
    GROUP BY coll_editoriales.ident
) as collections;