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
| DECLARE @t1 TABLE (Article1 varchar(255), Composant1 varchar(255))
DECLARE @t2 TABLE (Article2 varchar(255), Composant2 varchar(255))
INSERT INTO @t1 VALUES ('Boulon 1', 'Vis')
INSERT INTO @t1 VALUES ('Boulon 1', 'Ecrou')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Barre')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Support mural')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.0')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.1')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.2')
INSERT INTO @t1 VALUES ('Brol 1', 'comp 2.0')
INSERT INTO @t2 VALUES ('Boulon 2', 'Vis')
INSERT INTO @t2 VALUES ('Boulon 2', 'Ecrou')
INSERT INTO @t2 VALUES ('Boulon 2', 'Rondelle')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Barre')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Support mural')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.0')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.1')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.3')
INSERT INTO @t2 VALUES ('Brol 2', 'comp 2.0')
select
a.article1, b.article2
from
@t1 as a
inner join @t2 as b
on a.composant1 = b.composant2
inner join (select article1, count(*) as 'count'
from @t1
group by article1) c
on a.article1 = c.article1
inner join (select article2, count(*) as 'count'
from @t2
group by article2) d
on b.article2 = d.article2
group by a.article1, b.article2, c.count, d.count
having c.count = count(*) and d.count=count(*) |
Partager