CODEWITHSUNDEEP
sql
George
George

Reputation: 337

SQL Query NOT IN, EXIST

Schemas

Movie(title, year, director, budget, earnings)
Actor(stagename, realname, birthyear)
ActedIn(stagename, title, year, pay)
CanWorkWith(stagename, director)

I need to find all the actors (stagename and realname) that have never worked in a movie that has made a profit (Earnings > budget). SO finding all the bad actors :P

SELECT A.stagename, A.realname
FROM Actor A
WHERE A.stagename NOT IN
   (SELECT B.stagename
    FROM ActedIN B
    WHERE EXIST 
         (SELECT *
          FROM Movie M
          WHERE M.earnings > M.budget AND M.title = B.title AND M.year))

Would this find all the actors whose stagename does not appear in the second query? Second query will find all stagenames that acted in movies that made a profit.

Is this correct?

Upvotes: 4

Views: 227

Answers (5)

Zane Bien
Zane Bien

Reputation: 23125

SELECT 
    a.stagename,
    a.realname
FROM
    Actor a
LEFT JOIN
    ActedIn b ON a.stagename = b.stagename
LEFT JOIN
    Movie c ON b.title = c.title 
    AND a.year = b.year 
    AND c.earnings >= c.budget
WHERE 
    c.title IS NULL
GROUP BY
    a.stagename,
    a.realname

-No subqueries

-Accounts for actors who never acted in a movie yet

-Access to aggregate functions if needed.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270607

Yes, you have the right idea for using NOT IN, but you're missing half a boolean condition in the second subquery's WHERE clause. I think you intend to use AND M.year = B.year

WHERE M.earnings > M.budget AND M.title = B.title AND M.year = B.year

You can also do this with a few LEFT JOINs, looking for NULL in the right side of the join. This may be faster than the subquery.

SELECT
  A.stagename,
  A.realname
FROM Actor A
  LEFT OUTER JOIN ActedIN B ON A.stagename = B.stagename
  LEFT OUTER JOIN Movie M ON B.title = M.title AND B.year = M.year AND M.earnings > M.budget
WHERE
  /* NULL ActedIN.stagename indicates the actor wasn't in this movie */
  B.stagename IS NULL

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173562

That would do it. You could also write it like:

SELECT A.stagename, A.realname, SUM(B.pay) AS totalpay
FROM Actor A
INNER JOIN ActedIn B ON B.stagename = A.stagename
LEFT JOIN Movie M ON M.title = B.title AND M.year = B.year AND M.earnings > M.budget
WHERE M.title IS NULL
GROUP BY A.stagename, A.realname
ORDER BY totalpay DESC

It basically takes the movies that made a profit and uses that as a left join condition; when the left join is null it gets counted.

I've also added the total pay of said bad actors and ranked them from best to worst paid ;-)

Upvotes: 0

Jason Coyne
Jason Coyne

Reputation: 6636

That will work, but just do a join between ActedIn and Movie rather than exist.

Possibly also an outer join may be faster rather than the NOT IN clause, but you would need to run explain plans to be sure.

Upvotes: 0

dcp
dcp

Reputation: 55434

I think you could simplify it a bit, see below:

SELECT DISTINCT A.stagename, A.realname
FROM Actor A
WHERE NOT EXISTS
  (SELECT *
     FROM Actor B
        , Movie M
        , ActedIn X
    WHERE M.Title = X.Title
      AND X.StageName = B.StageName
      AND M.earnings > M.budget
      AND M.year = X.Year
      AND A.StageName = B.StageName)

Upvotes: 2

Related Questions