CODEWITHSUNDEEP
sql-serverselect
Victor
Victor

Reputation: 1271

Comparing two select statements

I need to write a stored procedure in T-SQL that would do the following:

  1. Get a list of items related to a certain SectionID
  2. Select and return all of the SectionIDs that have the same list of items or more (not less)

The table structure is as follows:

Section ID | Item Name
     1         Item1
     2         Item1
     1         Item2
     1         Item3
     2         Item2

So if I pass 1 as an ID, this should not return anything as SectionID 2 only has 2 of the 3 items that SectionID = 1 has, but if I pass SectionID = 2 as the parameter, this should return SectionID = 1.

Hopefully, I explained that properly. What would be a good approach for this?

Upvotes: 1

Views: 2562

Answers (2)

MyItchyChin
MyItchyChin

Reputation: 14031

Assuming the following Table...

DECLARE @Sections AS TABLE (Id INT, Item VARCHAR(25))

INSERT INTO @Sections
    (Id, Item)
    SELECT 1, 'Item1'
    UNION SELECT 2, 'Item1'
    UNION SELECT 1, 'Item2'
    UNION SELECT 1, 'Item3'
    UNION SELECT 2, 'Item2'

You can do this...

DECLARE @SectionId INT, @ItemCount INT

SELECT @SectionId = 2  --You'd change this to whatever
     , @ItemCount = 0

SELECT @ItemCount = COUNT(*)
FROM @Sections
WHERE Id = @SectionId

SELECT s.Id
FROM @Sections AS p
JOIN @Sections AS s
    ON s.Id != p.Id
    AND s.Item = p.Item
WHERE p.Id = @SectionId
GROUP BY s.Id
HAVING COUNT(*) >= @ItemCount

Upvotes: 2

aF.
aF.

Reputation: 66697

Here is a full example of what you need:

create table #test (
SectionID int,
ItemName varchar(10)
)

insert into #test values (1, 'Item1')
insert into #test values (2, 'Item1')
insert into #test values (1, 'Item2')
insert into #test values (1, 'Item3')
insert into #test values (2, 'Item2')
insert into #test values (3, 'Item1')
insert into #test values (3, 'Item2')
insert into #test values (3, 'Item3')
declare @test int
select @test = 3

declare @dist int
select @dist = count(distinct ItemName) from #test where SectionID = @test

select distinct t0.SectionID from #test t0
left join (select distinct SectionID, ItemName from #test where SectionID = @test) t1
  on t0.ItemName = t1.ItemName and t0.SectionID != t1.SectionID
where t0.SectionID != @test
group by t0.SectionID
having count(distinct t1.ItemName) >= @dist



drop table #test

In your case you just need this part:

declare @test int
select @test = 3 --input argument from stored procedure

declare @dist int
select @dist = count(distinct ItemName) from tablename where SectionID = @test

select distinct t0.SectionID from tablename t0
left join (select distinct SectionID, ItemName from tablename where SectionID = @test) t1
  on t0.ItemName = t1.ItemName and t0.SectionID != t1.SectionID
where t0.SectionID != @test
group by t0.SectionID
having count(distinct t1.ItemName) >= @dist

Upvotes: 3

Related Questions