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;

SELECT v.viewer_name, count(p.program_id)
FROM @viewers v
JOIN @viewership vs ON v.viewer_id = vs.viewer_id
JOIN @programs p ON vs.program_id = p.program_id
GROUP BY v.viewer_name;
--HAVING COUNT(DISTINCT p.program_id) > 1;

fork query

edited aug 12

created aug 12 anonymous