Only return rows when everything is correct

Question

In a small mental club, and I expect it to be easier than I can imagine. Received the following tables:

create table #x ( handid int, cardid int ) insert into #x values (1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8), (2,2),(2,3),(2,4),(2,300),(2,400),(2,500),(2,8), (3,2),(3,3),(3,4),(3,300),(3,400),(3,7),(3,8), (4,2),(4,300),(4,400),(4,500),(4,6),(4,7),(4,8) create table #winners(cardid int) insert into #winners values(300),(400),(500) select a.* from #xa inner join #winners b on a.cardid = b.cardid 

This returns the following:

I want this query to return strings when all three cardid exist for handid . Thus, the desired result set would not include handid 3.

This is a model of reality. In fact, #x contains 500 mill records.

EDIT

Good - there are actually winners consisting of data sets from #winners that have a variable number of entries. Therefore, changing the source code to the following, the result set should not include handid 1 or handid 3. I also get some unnecessary duplicate entries in the result set:

 create table #x ( handid int, cardid int ) insert into #x values (1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8000), (2,2),(2,3),(2,4),(2,300),(2,400),(2,500),(2,8), (3,2),(3,3),(3,4),(3,300),(3,400),(3,7),(3,8), (4,2),(4,300),(4,400),(4,500),(4,6),(4,7),(4,8) create table #winners(winningComb char(1), cardid int) insert into #winners values('A',300),('A',400),('A',500),('B',8000),('B',400) select a.* from #xa inner join #winners b on a.cardid = b.cardid 

Answer 1

You can use something like this:

 select handid from #x where cardid in (select cardid from #winners) group by handid having count(handid) = (select count(distinct cardid) from #winners); 

See SQL Fiddle with Demo

Result:

 | HANDID | ---------- | 2 | | 4 | 

Based on your editing, here is an attempt that returns the correct result, but I'm not sure if it will work with the large dataset that you have:

 ;with cte as ( select w1.cardid, w1.winningComb, w2.ComboCardCount from winners w1 inner join ( select COUNT(*) ComboCardCount, winningComb from winners group by winningComb ) w2 on w1.winningComb = w2.winningComb ) select a.handid from xa inner join cte b on a.cardid = b.cardid where a.cardid in (select cardid from cte) group by handid, b.ComboCardCount having COUNT(a.handid) = b.ComboCardCount 

See SQL Fiddle with Demo

Result:

 | HANDID | ---------- | 2 | | 4 | 

Answer 2

try the following:

 with cte as (select a.* from #xa inner join #winners b on a.cardid = b.cardid ), cte1 as (select *,ROW_NUMBER() over(partition by handid order by cardid) as row_num from cte), cte2 as (select handid from cte1 where row_num=(select COUNT(*) from #winners) ) select * from cte where handid in (select handid from cte2) 

SQL script demo

Answer 3

You can change your request to

 select a.*, count(a.*) as a_count from #xa inner join #winners b on a.cardid = b.cardid group by a.handid having a_count = 3 

Answer 4

The @Bluefleets (+1) approach looks good in terms of performance for a dataset; I think that with 500 million records, the performance profile will change.

I think OP wants to get the result in a slightly different format, something that adapts slightly to @Bluefleet code:

 select * from #x where handid in ( select handid from #x where cardid in (select cardid from #winners) group by handid having count(handid) = (select count(distinct cardid) from #winners) ) and cardid in (select cardid from #winners) 

I would also consider a solution to cursor smoke - since it can work better on a massive number of records depending on the data structure, indexes, number of winners, etc.

But without a complete dataset, which I can not say.