SELECT p.id,t.codeTypeGarant,t.libelle,p.codeGcAssureur,a.libelle AS Garant,p.codeLienParente,p.codeProduit,p.prime,p.primeMin,p.primeMax
FROM  primeproduit AS p
INNER JOIN gc_assureur AS a ON ((p.codeGcAssureur=a.codeGcAssureur) AND (p.codeSociete=a.codeSociete))
INNER JOIN gc_typegarant AS t ON (t.id=a.typeGarant)
INNER JOIN lienparente l ON (p.codeLienParente=l.codeLienParente)
-- WHERE p.id IN ( SELECT id FROM  primeproduit WHERE p.prime<=0  AND codeLienParente IN ('A'))
-- where p.prime<=0 -- and l.codeLienParente in ('A')
WHERE t.codeTypeGarant='CIE'
ORDER BY t.codeTypeGarant,codeGcAssureur,codeProduit,l.ordre