Finding difference within same table-CodePudding

I am trying to find difference in table depending on specific column.

I have a table which looks something like this:

 ------------------------ 
| rn | P_id   | D_id     |
 ------------------------ 
| 1  | 8      | 20       |
 ---- -------------- ---- 
| 2  | 13     | 20       |
 ---- -------------- ---- 
| 3  | 8      | 21       |
 ---- -------------- ---- 
| 4  | 13     | 21       |
 ---- -------------- ---- 
| 5  | 15     | 21       |
 ---- -------------- ---- 
| 6  | 17     | 21       |
 ------------------------

So , I want to get the P_id of rows where D_id is equal to 21 and is unique (For unique I mean there shouldn't be a row with with same P_id where D_id is equal to 20)

For example , in the shown table , expected result will be - P_id 15 and 17.

Would like get this result using JOIN if possible.

CodePudding user response：

WITH CTE(RN,P_ID,D_ID) AS
(
  SELECT 1 ,  8 ,      20 UNION ALL      
  SELECT 2 ,  13 ,     20   UNION ALL       
  SELECT 3 ,  8  ,     21 UNION ALL         
  SELECT 4  , 13  ,    21 UNION ALL         
  SELECT 5   ,15 ,     21 UNION ALL         
  SELECT  6  , 17,      21      
)
SELECT C.P_ID 
FROM CTE AS C
GROUP BY C.P_ID
HAVING MAX(C.D_ID)=MIN(C.D_ID) AND MAX(C.D_ID)=21

CodePudding user response：

If you have to use a join, you should use a subquery in which you fetch the number of occurences of a P_id.

This query fetches the number of occurences:

SELECT `P_id`, COUNT(`rn`) AS `cnt`
FROM `table`
GROUP BY `P_id`;

That would mean that the entire query becomes something like this:

SELECT t.`P_id`
FROM `table` t
INNER JOIN ( SELECT `P_id`, COUNT(`rn`) AS `cnt`
             FROM `table`
             GROUP BY `P_id` ) c
ON c.`P_id` = t.`P_id`
WHERE t.`D_id` = 21 AND c.`cnt` = 1;