Get Common values based on type in SQL-CodePudding

i need to get common TaskNo based on TaskType in Sql

here i share my table

TaskType TaskNo
100652  100
100652  200
100652  300
100556  100
100556  200
100556  300
100556  500
100556  600
100556  700
100556  800

now i need to get only Common taskNo based on TaskType (its possible TaskType is more then 2)

TaskType TaskNo
100652  100
100652  200
100652  300
100556  100
100556  200
100556  300

CodePudding user response：

Assuming you want to find all records of task numbers which are associated with all task types:

WITH cte AS (
    SELECT TaskNo
    FROM yourTable
    GROUP BY TaskNo
    HAVING COUNT(DISTINCT TaskType) = (SELECT COUNT(DISTINCT TaskType) FROM yourTable)
)

SELECT TaskType, TaskNo
FROM yourTable
WHERE TaskNo IN (SELECT TaskNo FROM cte);

CodePudding user response：

Select taskType, taskNo
from myTable t1
where exists (
   select * from myTable t2 
   where t1.TaskNo = t2.taskNo and t1.taskType <> t2.taskType
);

DBFiddle demo