Le portail francophone de la géomatique

WITH foncier as(SELECT p.dnupro, 
    CASE
        WHEN p.dlign6 in ('67440 THAL-MARMOUTIER', '67700 HAEGEN','67440 MARMOUTIER','67440 REINHARDSMUNSTER') THEN 'Thal et environs'
        WHEN p.dlign6 like '67%' THEN 'Bas-Rhin'
        ELSE 'Hors département'
    END AS situation,
    Count(p.idu) cpt_pro, p.idu
    FROM proprietaires p
    GROUP BY p.dnupro, situation, idu), 
    p_t as(
        SELECT DISTINCT f1.dnupro, f2.situation, f1.idu
            FROM foncier f1
        JOIN foncier f2 ON f1.dnupro = f2.dnupro
        WHERE f2.situation = 'Thal et environs'), 
    p_d as(
    SELECT DISTINCT f1.dnupro, f2.situation, f1.idu
        FROM foncier f1
        JOIN foncier f2 ON f1.dnupro = f2.dnupro
    WHERE f2.situation = 'Bas-Rhin'), 
    p_h as(
    SELECT distinct f1.dnupro, f2.situation, f1.idu
    FROM foncier f1
    JOIN foncier f2 ON f1.dnupro = f2.dnupro
    WHERE f2.situation = 'Hors département'), 
    p_d_t as (
    SELECT * FROM p_t
        UNION 
        SELECT * from p_d
        WHERE not exists (SELECT 1 FROM p_t WHERE p_t.dnupro = p_d.dnupro))

    SELECT * FROM p_d_t
    UNION
    SELECT * FROM p_h
        WHERE not exists (SELECT 1 FROM p_d_t WHERE p_d_t.dnupro = p_h.dnupro)