How to get unique date from multiple dates based on condition in PostgreSQL?-CodePudding

How do I get unique dates from the table "timelog" for the dates where isParent is not 1.

In the table, 2022-01-10 should not come as the result as this date has isParent as 1.

So far, I have written query like this -

SELECT DISTINCT session::date 
FROM timelog
WHERE id IN (SELECT id FROM timelog WHERE isParent = 0)

Obviously, this is not working as intended. What changes do I need to make in this query to make it work?

CodePudding user response：

You can try to use condition aggregate function in HAVING, let your condition which didn't any isParent = 1 date on CASE WHEN

SELECT session::date
FROM timelog 
GROUP BY session::date
HAVING COUNT(CASE WHEN isParent = 1 THEN 1 END) = 0

SELECT session::date
FROM timelog 
GROUP BY session::date
HAVING COUNT(*) filter(where isParent = 1) = 0

CodePudding user response：

SELECT session::date
FROM timelog
GROUP BY session::date
HAVING NOT ARRAY_AGG(isParent) && ARRAY[1]; -- NOT in array containing 1

CodePudding user response：

Something along the lines of

SELECT DISTINCT session::date FROM timelog WHERE isParent = 0

should work. All you are looking for is unique dates where the parent is 0 Correct? No aggregation needs to be applied ?