August 17, 2014 at 8:34 pm
Hello,
I'm practicing SQL on SQLzoo.net and using the table at http://sqlzoo.net/wiki/More_JOIN_operations
Based on the table on there, I am trying to answer this question:Bring back movie title and names of any actor who has worked with another actor on more than one movie.
Can anyone help?
August 18, 2014 at 6:01 am
You'll need to JOIN between Casting and Actor to get the actors. You'll want to GROUP BY a count of the actors from the Casting table for each movie. Then, once you have put those together, JOIN back to the Movie again.
Try that out. If you get stuck, post what you've tried that's not working.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2014 at 11:39 am
Hello, thanks for the reply.
Maybe I misunderstood your reply but doing that only gives me the total count of actors in each movie. It doesn't tell me who worked with who nor does it tell me how many times they worked together.
select title, count(actorid) from movie, actor, casting
where movie.id=casting.movieid and actorid=actor.id
group by title
August 18, 2014 at 12:17 pm
FYI: He said to group by the count of actors, not by the title.
EDIT: Look up the HAVING clause.
August 18, 2014 at 12:34 pm
I'm really worried on what you might be learning on that site. You're using the old-join style which might seem fine for inner joins but it'll became more confusing with outer joins because you might need to use mixed ways to join tables. I strongly suggest you to use new-join style (using the JOIN...ON... clause) and to alias your tables and use the alias to prefix all of your columns.
To help you with your problem, I'd use a self-join which would also be a non-equi join (partially) and the HAVING clause as stated by Brandie.
August 18, 2014 at 1:22 pm
We do the "from table" like this at work, I always just thought that's how PL/SQL's syntax is and not a matter of old vs new. Good to know.
With the self join hint I tried doing something like this just on the casting table:
select Concat(act1, ' ', act2) from (select a.movieid, a.actorid act1, b.actorid act2 from casting a, casting b
where a.movieid=b.movieid and a.actorid not like b.actorid) a
group by Concat(act1, ' ', act2)
having count(Concat(act1, ' ', act2)) > 1
basically did a concat of any 2 actor combination for every movie, and if there's a repeat of the concat in any movie then they worked together twice.
I couldn't run this on the site, apparently the query takes too long doing it this way. I feel like there might be a easier way to do this though. Not sure if this is the right way to do things.
August 18, 2014 at 1:41 pm
Have you tried removing the CONCAT function from the GROUP BY and HAVING clauses? Just use the columns directly on group by and remove them from having.
August 18, 2014 at 5:59 pm
If i remove the Concat from the having, then wouldn't it just bring back any single actor that has appeared twice instead of the condition I want?
August 18, 2014 at 6:40 pm
No, the expression inside a COUNT() will only matter if it can return null values. Otherwise, is the same to use a constant, an asterisk or any non nullable column. With a nullable column it will discard the null values in the count. Since you can't have a null value in that expression, then you don't need it.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply