Only return rows when all are true

Question

In a slight mental tangle and I expect this is easier than I imagine. Got 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 
        #x a 
        inner join #winners b
            on
            a.cardid = b.cardid 

This returns the following:

I only want this query to return the rows when all of the three cardids exists for a handid. So the desired result set would not include handid 3.

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

EDIT

Ok - there are actually winners that are made up of sets of data from #winners which have a variable number of records. So amending the original code to the following the result set should not include handId 1 or handId 3. I am also getting some unwanted duplicate records 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 
        #x a 
        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 edit, here is an attempt that returns the correct result however I am not sure if it will work with the larger 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 x a
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

@Bluefleets (+1) approach looks good in terms of performance for the data set; I guess with 500 million records the performance profile will change.

I think the OP wants the output in a slightly different format, something that a slight adaptation to @Bluefleet's code produces:

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 the dread cursor solution - as it may perform better over a massive number of records depending on the data structure, indexes, number of winners and so on.

But without the full dataset I cant really say.

Answer 3

try this:

with cte as
(select a.* 
from 
        #x a 
        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 fiddle demo

Answer 4

You can change your query to

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