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 38 39 40 41 42 43 44 45 46 47 48 49 50
|
with bd (numbd, nomalbum)
as
(
select 1, 'Gare aux gaffes du gars gonflé'
union all
select 2, 'Le cas Lagaffe'
union all
select 3, 'Gaffes, bévues et boulettes'
),
magasin (nummag, nommag)
as
(
select 1, 'Paris'
union all
select 2, 'Dijon'
union all
select 3, 'Lyon'
union all
select 4, 'Nice'
),
catalogue (nummag, numbd, nbexempdispo)
as
(
select 1, 1, 0
union all
select 1, 2, 3
union all
select 1, 3, 1
union all
select 2, 1, 3
union all
select 2, 3, 10
union all
select 3, 1, 4
union all
select 3, 2, 5
union all
select 3, 3, 10
union all
select 3, 4, 9
union all
select 5, 1, 4
)
select m.nummag, m.nommag
from bd b
cross join magasin m
left outer join catalogue c on c.nummag = m.nummag and c.numbd = b.numbd and c.nbexempdispo > 0
group by m.nummag, m.nommag
having count(b.numbd) = count(c.numbd); |
Partager