Br0dskive Br0dskive - 1 month ago 13
SQL Question

SQL find values connected to more than 199 unique values

I asked this question earlier today but I believe I was not being clear enough.

My problem is this:
What actors (name and number of movies) have played roles with a unique character name in more than 199 movies?

I have these tables:

Table "public.filmparticipation"
Column | Type | Modifiers
----------+---------+-----------
partid | integer |
personid | integer | not null
filmid | integer | not null
parttype | text | not null


Table "public.filmcharacter"
Column | Type | Modifiers
---------------+---------+-----------
partid | integer |
filmcharacter | text |
billingpos | integer |


Table "public.person"
Column | Type | Modifiers
-----------+--------------+-----------
personid | integer |
lastname | text | not null
firstname | text |
gender | character(1) |


To clarify the problem:
I want to find all the actors who have played in more than 199 movies, where their character name is unique. This means that the character name they had in this movie has not been used in any other movie in the database. I want to get a list with the name of the actor, and the number of movies they have played in where their character name was unique. The result would look something like this (I used totally random values):

Name | Filmcharacter|
-----------+--------------+
Pitt | 465 |
Clooney | 265 |
Depp | 320 |
Jolie | 229 |

Answer

Try this:

Select p.personId, p.firstName, p.LastName, count(*)
from public.Person p
   join public.filmparticipation fp
      on fp.personId = p.personId
             -- subquery ensures that the part has not been in any other film
         and (Select count(*) from public.filmparticipation
              where partId = fp.PartId) == 1
Group By p.personId, p.firstName, p.LastName
Having count(*) > 199    -- <-- this filters out actors in less than 199 films