How to make a comparison for the record that has r…

I have first table that has columns:

   1. id
   2. key
   3. value

And second table(more like the list):

key

I need to get distinct id that contains all keys from second table

I have tried self join but it is very slow. Also I tried COUNT = COUNT but performance the same.

Self join:

select  f.id from first
    join first f2 on f.id = f2.id AND f2.key  = f. key

COUNT:

select a.keyfrom @a a
    where ( select SUM(CASE WHEN k.[key] is not NULL THEN 1 ELSE 0 END) from [b] b
        LEFT JOIN Second s on s.key= b.[Key]
     where b.[Key] = a.key) = @KeyCount

Answer

You can also check this-

SELECT A.id
FROM TAB1 A
INNER JOIN TAB2 B ON A.[key] = B.[Key]
GROUP BY A.id
HAVING COUNT(DISTINCT A.[key]) 
    = (SELECT COUNT(DISTINCT [Key]) FROM TAB2)

How to make a comparison for the record that has rows to another rows? [closed]

Advertisement

Answer