I want to avoid selecting the cases where there is a blank child, but only where a parent-child relationship already exists. So in this example, A-D would stay but the empty rows with A should get removed. As C doesn't have any children in any row, it will remain.

Input:

parent     child
A          D
A
A
B          E
B          F
C

Desired Output:

parent     child
A          D
B          E
B          F
C

What I've tried:

SELECT parent, child
FROM my_table
WHERE child NOT NULL
EXCEPT 
SELECT parent, child
FROM my_table
-- condition to check existing relationships if they exist

CodePudding user response：

maybe use a query like below. this uses exists keyword to check presence of any other not null child

SELECT parent, child
FROM my_table t
WHERE child is NOT NULL
OR NOT EXISTS 
(
SELECT 1 FROM my_table p Where p.parent=t.parent and p.child is Not null
)

CodePudding user response：

You can try to use two queries with UNION ALL one is getting result by child IS NOT NULL another is getting result by condition aggregate function.

SELECT parent,child
FROM my_table
WHERE child IS NOT NULL
UNION ALL
SELECT parent,child
FROM my_table
GROUP BY parent,child
HAVING COUNT(CASE WHEN child IS NULL THEN 1 END) = 1

or another way you might make a trick let condition in HAVING to make it simple.

SELECT parent,child
FROM my_table
GROUP BY parent,child
HAVING COUNT(CASE WHEN child IS NULL THEN 1 END) = 1 OR child IS NOT NULL 

CodePudding user response：

This is essentially 2 queries so we can join them with UNION ALL

• the first query is where there is a child child is not null
• the second query is where the number of children is 0. By default COUNT() does not include null values. We also use GROUP BY which means that duplicate rows will be combined.

SELECT parent, child FROM t
WHERE child IS NOT NULL
UNION ALL
SELECT parent, null FROM t
GROUP BY parent
HAVING COUNT(child) = 0;

parent | child
:----- | :----
A      | D    
B      | E    
B      | F    
C      | null 

db<>fiddle here