create table #MyList(L char(2), T char(2))
go
insert into #MyList values('L1', 'T1')
insert into #MyList values('L1', 'T2')
insert into #MyList values('L1', 'T3')
insert into #MyList values('L1', 'T4')
go
create table #Things(T char(2), C char(2))
go
insert into #Things values('T1', 'C1')
insert into #Things values('T2', 'C2')
insert into #Things values('T3', 'C1')
insert into #Things values('T4', 'C2')
go
create table #Categories(C char(2), EL char(2))
insert into #Categories values('C1', 'E1')
insert into #Categories values('C2', 'E2')
insert into #Categories values('C3', 'E3')
insert into #Categories values('C4', 'E1')
go
create table #ElementsList(EL char(2), E char(2))
insert into #ElementsList values('E1', 'a')
insert into #ElementsList values('E1', 'b')
insert into #ElementsList values('E1', 'c')
insert into #ElementsList values('E2', 'a')
insert into #ElementsList values('E2', 'b')
insert into #ElementsList values('E2', 'd')
insert into #ElementsList values('E2', 'e')
insert into #ElementsList values('E3', 'c')
go


declare @L char(2)
set @L = 'L1';
select E, count(E) from #ElementsList
where EL in (select distinct EL
                  from #Categories
                  inner join #Things on #Things.C = #Categories.C
                  inner join #MyList on #MyList.T = #Things.T
                  where #MyList.L = @L)
group by E
having count(E) = (select count(distinct EL)
                  from #Categories
                  inner join #Things on #Things.C = #Categories.C
                  inner join #MyList on #MyList.T = #Things.T
                  where #MyList.L = @L)

SELECT e.e, COUNT( * ) FROM #ElementsList AS e
JOIN (
        SELECT c.EL , COUNT( * )
        FROM #Categories AS c
        JOIN #Things AS t ON t.C = c.C
        JOIN #MyList AS m ON m.T = t.T
        WHERE m.L = @L
        GROUP BY EL
) AS e1( el , cnt )
ON e.el = e1.el
GROUP BY e.e
HAVING COUNT( * ) = MIN( e1.cnt )

This page was last updated on May 01, 2006 04:28 PM.