Geek Answers Handbook

SQL query NOT IN, EXIST

Schemes

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) who never worked in a movie that made a profit (profit> budget). So find 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))

Will it find all the actors whose name does not appear in the second query? The second query will find all the statuses that acted in the films that made a profit.

Is it correct?

+4
source share
5 answers
 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 have not played in the movie

-Access for aggregation of functions, if necessary.

0
source

I think you can 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)
+2
source

Yes, you have the right idea to use NOT IN , but in the second WHERE subquery, you are missing half of the logical condition. 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 JOIN s by looking for NULL on the right side of the connection. It can be faster than a 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
0
source

This will work, but just make the connection between ActedIn and Movie and not existing.

It may also be that the outer join may be faster than the NOT IN clause, but you will need to follow the explanation plans to be sure.

0
source

That would do it. You can also write it like this:

 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

Basically, it makes films that are profitable, and uses it as a condition of left joining; when the left join is null, it counts.

I also added the total salary of these bad actors and rated them from best to worst paid; -)

0
source

More articles:


All Articles