I'm trying to get all the duplicated rows based on a certain conditions in a table. I'm unable to get it. Here is a look at the table.

Table:

MasterId	TaskId	PostType
1	t1	movies
1	t1	music
2	t2	movies
2	t2	movies
2	t2	movies
3	t3	news
4	t4	movies

MasterId has multiple TaskIds and tasksId's will have multiple 'PostType'.

I 'm trying to get masterId's that have PostType only 'movies' and not 'news' or 'music'.

CodePudding user response：

You can use conditional aggregation logic next to HAVING clause after GROUPing BY MasterId column such as

SELECT MasterId
  FROM t
 GROUP BY MasterId   
HAVING SUM(CASE WHEN PostType = 'movies' THEN 1 ELSE 0 END) = COUNT(*)

Demo

CodePudding user response：

seems like an normal selection clausule that you're looking for.

try this

select 
    t1.MasterId, 
    t1.TaskId 
from dbo.Table t1
where t1.PostType = 'movies'
and t1.MasterId not in (
    select t2.MasterId 
    from dbo.Table t2 
    where t2.MasterId = t1.MasterId
    and t2.PostType <> 'movies'
)