I have these data records:

ImageId     ParentId
image1      Null      // wanna have
image1      1         // wanna have
image3      2        
image2      1        

How should my query be written that I get the records with same ImageId AND the other row of the Pair result having always ParentId value Null?

CodePudding user response：

If you only want the ImageID where there is a row with ParentID null and a row with ParentId = 1 here are 2 queries that you can use.

select a.ImageId
from table_name a
join table_name b on a.ImageId = b.ImageId
where a.ParentID is null
and b.ParentID = 1;

| ImageId |
| :------ |
| image1  |

select ImageId
from table_name
where ParentID is null or ParentID = 1
group by ImageId
having count(distinct coalesce(ParentID,999)) = 2;
GO

| ImageId |
| :------ |
| image1  |

db<>fiddle here

CodePudding user response：

We can try to use CTE to find ParentId IS NULL ImageId and then use that to judgment by EXISTS

;WITH CTE AS (
    SELECT *
    FROM T
    WHERE ParentId IS NULL
)
SELECT ImageId,ParentId
FROM (
  SELECT *,
         COUNT(*) OVER(PARTITION BY ParentId) cnt
  FROM T 
) t1 
WHERE cnt > 1 AND 
EXISTS (SELECT 1 FROM CTE c WHERE c.ImageId = t1.ImageId)
UNION ALL
SELECT ImageId,ParentId
FROM CTE

or we can use count window function with your logic to make it simple.

SELECT ImageId,ParentId
FROM (
  SELECT *,
         COUNT(*) OVER(PARTITION BY ParentId) cnt
  FROM T 
) t1 
WHERE (cnt > 1 AND 
  EXISTS (
    SELECT 1 FROM T tt 
    WHERE tt.ImageId = t1.ImageId 
    AND tt.ParentId IS NULL
  )
)
OR ParentId IS NULL

sqlfiddle

EDIT

I saw you edit your question, we can just use COUNT window function to get duplicate rows by PARTITION BY your identifier columns

SELECT ImageId,ParentId
FROM (
  SELECT *,
         COUNT(*) OVER(PARTITION BY ImageId) cnt
  FROM T 
) t1
WHERE cnt > 1

sqlfiddle

CodePudding user response：

If what you want to do is to find the images having one or more null values and one of more not null values we can use the fact that COUNT() does not include null values.

• 'count(ParentID) > 0` checks that there is at least one not null value of ParentID
• count(coalesce(ParentID,1)) <> count(ParentID) checks that there is at least one null value of ParentID, which will be counted in the first expression because it will have been changed to 1 by coalesce.
  We use string_agg to show the parentID's present for the image.

select 
  ImageId,
  String_agg(ParentID,',') "ParentID's"
from table_name
group by ImageID
having count(ParentID) > 0
and count(coalesce(ParentID,1)) <> count(ParentID)
GO

ImageId | ParentID's
:------ | :---------
image1  | 1,2       
image3  | 2         

db<>fiddle here