Logical conditional in PostgreSQL

Question

I want the name of every male director that has directed more than 20 movies and has cast in every single movie he has directed. If he has cast a movie, but hasn't directed it, that's okay and I still want his name; if he has directed any movie but hasn't cast in it, I don't want him anymore.

SELECT p.firstname, 
    p.lastname 
FROM person p 
WHERE p.gender = 'M' AND
(
    SELECT COUNT(*)
    FROM filmparticipation fpd 
    WHERE p.personid = fpd.personid AND
        fpd.parttype = 'director' AND
        (
            SELECT COUNT(*) 
            FROM filmparticipation c 
            WHERE c.personid = fpd.personid AND
                c.filmid = fpd.filmid AND
                c.parttype = 'cast'
        ) > 0
) >= 20;

This is just one of my multiple tries. As a side question, why doesn't it work? I make sure I'm checking the correct directed movie-id to the casted movie-id etc.

I don't see your table definitions anywhere? Or the version of PostgreSQL ... — Erwin Brandstetter, Oct 31 '12 at 20:37

Erwin Brandstetter · Accepted Answer · 2012-10-31T21:12:21.180

Could look like this:

SELECT p.firstname, p.lastname 
FROM   person p
JOIN  (
   SELECT fd.personid
   FROM   filmparticipation fd
   LEFT   JOIN filmparticipation fc USING (personid, filmid)
   WHERE  fd.parttype = 'director'
   AND    fc.parttype = 'cast'
   GROUP  BY fd.personid
   HAVING count(*) > 20  -- 21+ movies directed (NULL not counted)
   AND    NOT bool_or(fc.personid IS NULL) -- no movie directed but not cast
   ) AS fp USING (personid)
WHERE  p.gender = 'M';

In the subquery I LEFT [OUTER] JOIN all rows where the same person for the same film directed and casted - Where (s)he only directed the rest is filled with NULL. That's what a LEFT JOIN does. This assumes that the same person can only appear once in the same role in the same film (UNIQUE or PK constraint!).

Grouping by personid has to result in more than 20 rows and none of the casting personid can be NULL. JOIN to the person table and restrict to males and you are done.

There are many ways to solve it, this should be one of the fastest.

Logical conditional in PostgreSQL

1 Answers1