0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software quality control experts, automation engineers, and software testers

declare @viewers table (viewer_name varchar(1000), viewer_id int);
declare @viewership table (viewer_id int, program_id int);
declare @programs table (program_id int, program_name varchar(100));

insert into @viewers
select top(10) DisplayName, Id
from Users
order by Id asc;

insert into @viewership
select top(10) Id, 
100 as program_id
from Users
order by Id asc;

insert into @viewership
select top(10) Id, 
101 as program_id
from Users
order by Id asc;

insert into @programs
select 100 as program_id, 'Program 1' as program_name;

insert into @programs
select 101 as program_id, 'Program 2' as program_name;

WITH ViewerProgramCounts AS (
  SELECT
    v.viewer_name,
    p.program_name,
    COUNT(*) AS program_count
  FROM
    @viewers v
  INNER JOIN @viewership vw ON v.viewer_id = vw.viewer_id
  INNER JOIN @programs p ON vw.program_id = p.program_id
  GROUP BY
    v.viewer_name, p.program_name
)
SELECT
  viewer_name,
  program_name
FROM
  ViewerProgramCounts
WHERE
  program_count > 1;

fork query

edited aug 12

created aug 12 anonymous