SELECT uid FROM PLAY_SONG GROUP BY uid HAVING COUNT(DISTINCT songid) > 9

select distinct
   t1.uid,
   t3.uid
from
   PLAY_SONG t1
   inner join PLAY_SONG t3 on t3.uid <> t1.uid
where
   exists (select * from PLAY_SONG t2 where t2.uid = t1.uid having COUNT(*) > 9)
   and not exists
   (
      select songid from PLAY_SONG t4 where t4.uid = t1.uid
      except
      select songid from PLAY_SONG t5 where t5.uid = t3.uid
   )
order by
   t1.uid,
   t3.uid

create table PLAY_SONG (uid int, songid int)
 
insert into PLAY_SONG values
(1,1),(1,2), (1,3), 
(2,1), (2,2), (2,5), 
(3,1),(3,2), (3,5), 
(4,1),(4,2),
(5,1),(5,2),(5,3), (5,5);

-- insert query from my previous post here but change 9 to 2

with cte as
(
   select distinct
      t3.uid uid1,
      t1.uid uid2,
      rank() over (order by t3.uid) as rn
   from
      PLAY_SONG t1
      inner join PLAY_SONG t3 on t3.uid <> t1.uid
   where
      exists (select * from PLAY_SONG t2 where t2.uid = t1.uid having COUNT(*) > 2)
      and not exists
      (
         select songid from PLAY_SONG t4 where t4.uid = t1.uid
         except
         select songid from PLAY_SONG t5 where t5.uid = t3.uid
      )
   )
select
  uid1,
  uid2
from
   cte c1
where
   not exists 
   (  
      select * from cte c2 
      where c2.uid1 = c1.uid2 and c2.uid2 = c1.uid1 and c2.rn < c1.rn
   )
order by
   uid1,
   uid2

--- CREATE SAMPLE TABLE AND DATA ---------

--- table
create table PLAY_SONG (uid int, songid int)

--- sample data. uid 4 is has repeated songid 1, so that should
-- not appear in the results.
insert into PLAY_SONG values
(1,1),(1,2), (1,3), (1,4),(1,5),(1,6),(1,7), (1,8), (1,9), (1,10),
(2,1),(2,2), (2,3), (2,4),(2,5),(2,6),(2,7), (2,8), (2,9), (2,10),
(3,1),(3,2), (3,3), (3,4),(3,5),(3,6),(3,7), (3,8), (3,9), (3,10),
(3,11),(3,12), (3,13), (3,14),(3,15),(3,16),(3,17), (3,18), (3,19), (3,20),
(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1),(4,1);

--- QUERY  --------- --------- -----------

-- the first CTE picks distinct uid+song combinations.
with DISTINCT_PLAY_SONG as
(
   select distinct uid,songid from PLAY_SONG
)
-- the second cte has the logic to pick uids who have
-- a) played all songs played by another uid AND
-- b) only if that second uid has played at least 10 songs.
,cte as
(
   select distinct
      t3.uid uid1,
      t1.uid uid2,
      rank() over (order by t3.uid) as rn
   from
      DISTINCT_PLAY_SONG t1
      inner join DISTINCT_PLAY_SONG t3 on t3.uid <> t1.uid
   where
      exists (select * from DISTINCT_PLAY_SONG t2 where t2.uid = t1.uid having COUNT(*) > 9)
      and not exists
      (
         select songid from DISTINCT_PLAY_SONG t4 where t4.uid = t1.uid
         except
         select songid from DISTINCT_PLAY_SONG t5 where t5.uid = t3.uid
      )
   )
-- the final select is simply presenting the data from the
-- previous CTE, except, it is removing duplicate permutations.
select
  uid1,
  uid2
from
   cte c1
where
   not exists 
   (  
      select * from cte c2 
      where c2.uid1 = c1.uid2 and c2.uid2 = c1.uid1 and c2.rn < c1.rn
   )
order by
   uid1,
   uid2;

quote:

---

Originally posted by Renogen

now i have to study your query for my exams.

---

quote:

---

Originally posted by nigelrivett

maybe

select uid2 = p1.uid, uid1 = p2.uid
from PLAY_SONG p1
left join PLAY_SONG p2
on p1.uid <> p2.uid
and p1.songid = p2.songid
group by p1.uid, p2.uid
having count(distinct p1.songid) >= 10
and count(distinct p1.songid) = count(distinct p2.songid)

You could turn p1 and p2 into derived tables and get distinct songids per uid.
If you do that then you wouldn't need the distincts in the group by..

select uid2 = p1.uid, uid1 = p2.uid
from (select distinct uid, songid from PLAY_SONG) p1
left join (select distinct uid, songid from PLAY_SONG) p2
on p1.uid <> p2.uid
and p1.songid = p2.songid
group by p1.uid, p2.uid
having count(p1.songid) >= 10
and count(p1.songid) = count(p2.songid)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---