PostgreSQL Query – Find Parent Records Where All Child Records Have a Given Value

postgresql

An Event has many Participants. A Participant has a field of "status".

I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'.

I can find all Events where some of its Participants have a status of 'present' with the following SQL:

SELECT events.id, participants.status as status, count(*) 
FROM `events` INNER JOIN `participants` 
ON `participants`.`event_id` = `events`.`id` 
WHERE (`participants`.`status` != 'present') 
GROUP BY participants.event_id HAVING count(*) > 0

This almost works. The problem is that if one of the participant rows has a status of something other like "away", the event will still get fetched, because the sibling records are of a status equal to "present".

I need to ensure that I am filtering out every Event record with all Participants of a status of "present".

I am using PostgreSQL, so no non-PostgreSQL specific solutions please.

Thanks.

Best Answer

I don't have a copy of PostgreSQL in front of me. Does this compile in your version:

...
HAVING count(*) = (select count(*) from participatns as x where x.event_id = events.id)

An alternative approach would be to remove from this list of all events those which have a participant who's status is not 'present', using the EXCEPT clause:

select
    event.id
from events

EXCEPT

select
    event.id
from event
INNER JOIN participants 
ON participants.event_id = events.id 
WHERE (participants.status != present)

The EXCEPT syntax is usually more efficiently expressed as NOT EXISTS or as a LEFT JOIN with the child identifer ends up as null. I'm sure you can re-factor the query to suit your needs.

Related Solutions

Postgresql – Postgres: Given some parent id’s how can I SELECT X child records for each

Assuming the "first" is defined through the id column in the child table, something like this should work:

select p.id, p.name, c.name
from parent p 
  join (
     select c.name, 
            c.parent_id, 
            row_number over (partition by c.parent_id order by c.id) as rn
     from child c
  ) ch
   on ch.parent_id = p.id 
  and ch.rn <= 3
where p.id in (1,3,4);

PostgreSQL – Using First CTE as Filter for Second CTE

Looking into my crystal ball, your query might work faster by orders of magnitude like this:

SELECT i.*, rr.urllist
FROM   vw_image i
LEFT   JOIN LATERAL (
   SELECT string_agg(r.url, ',') AS urllist
   FROM   resourceonpage rop
   JOIN   resource r ON r.id = rop.pageid
   WHERE  rop.siteid   = 2294  -- or: = i.siteid
   AND    r.resourceid = i.resourceid
   ) rr ON true
WHERE  i.siteid = 2294
AND    i.childtype = 'i'
ORDER  BY i.id
OFFSET 1 ROWS
FETCH  NEXT 50 ROWS ONLY;  -- or just: OFFSET 1 LIMIT 50

You don't need CTEs here, those will only slow you down.

I use a LATERAL join to use indexes on resourceonpage and resource and aggregate in the subquery right away. About LEFT JOIN LATERAL ... ON true:

What is the difference between LATERAL and a subquery in PostgreSQL?

Looking even deeper into my crystal ball, the best indexes might be:

CREATE INDEX ON vw_image (siteid, id) WHERE childtype = 'i';
CREATE INDEX ON resourceonpage (siteid, pageid);
CREATE INDEX ON resource (resourceid, id, url);

Appending url is only useful if you get index-only scans out of it.

An alternative approach would be to use parentheses in the FROM list like demonstrated here:

How to include missing data for multiple groupings within the time span?

But, according to my crystal ball (and an educated guess) the LATERAL version will be faster. There is not enough information in the question to give you more than educated guesses.

Best Answer

Related Solutions

Postgresql – Postgres: Given some parent id’s how can I SELECT X child records for each

PostgreSQL – Using First CTE as Filter for Second CTE

Related Question