CODEWITHSUNDEEP
sqlrelational-algebra
Synor
Synor

Reputation: 353

SQL inner join with count() condition, and relationnal algebra

I have these tables:

And I would like to count the number of actors for each movie and then only get movies with more than 10 actors. I have a query to count the number of actors for each movie, which goes like this:

SELECT idmovie, count(actor) FROM Cast GROUP BY idmovie HAVING count(actor) > 10;

Now, I wonder how to get that result and join it to the Movies table. I tried:

SELECT name FROM Movies INNER JOIN (SELECT idmovie FROM Cast GROUP BY idmovie HAVING count(actor) >2) Cast ON Cast.idmovie = Movies.id;

But it doesn't work.

I also have to translate it to relational algebra.

π name (σ (count(σ id = idmovie))) Movies⨝Cast

Which is obviously wrong...

Any help?

Upvotes: 0

Views: 3731

Answers (2)

japzdivino
japzdivino

Reputation: 1746

Try this...

SELECT m.name, COUNT(c.actor) AS 'ActorsCount' 
FROM Movies m INNER JOIN [Cast] c ON m.id = c.idmovie
GROUP BY m.name HAVING COUNT(c.actor) > 10;

Upvotes: 2

Oliver Dain
Oliver Dain

Reputation: 9953

The query looks correct to me except perhaps that you aliased the nested query with Cast which is also the name of a table. I'm not sure what effect that'd have but I'd expect it to confuse MySQL. Try the following:

SELECT name FROM Movies INNER JOIN
(SELECT idmovie FROM Cast GROUP BY idmovie HAVING count(actor) >2) CastCount
ON CastCount.idmovie = Movies.id;

I didn't try it, but I think that'll work

Upvotes: 0

Related Questions