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
|
WITH magasins AS
(
select 1 id_mag, 'mag 1'nom_mag FROM dual union
select 2 , 'mag 2' FROM dual union
select 3 , 'mag 3' FROM dual union
select 4 , 'mag 4' FROM dual union
select 5 , 'mag 5' FROM dual union
select 6 , 'mag 6' FROM dual union
select 7 , 'mag 7' FROM dual),
Enseigne_assoc as
(
select 1 id_enseigne_assoc, 1 ref_enseigne, 1 ref_magasin FROM dual union
select 2 , 2 , 1 FROM dual union
select 3 , 3 , 2 FROM dual union
select 4 , 1 , 3 FROM dual union
select 5 , 3 , 4 FROM dual union
select 6 , 2 , 4 FROM dual
)
SELECT max(A.ref_enseigne)ref_enseigne,A.ref_magasin, B.nom_mag
FROM Enseigne_assoc A join magasins B on B.id_mag=A.ref_magasin
WHERE exists (SELECT * FROM enseigne_assoc C
where A.id_enseigne_assoc=C.id_enseigne_assoc
and C.ref_enseigne in(1,2))
GROUP BY ref_magasin,B.nom_mag |
Partager