CODEWITHSUNDEEP
mysqlsql
Mysterious Otter
Mysterious Otter

Reputation: 4049

Most Efficient way to write HAVING condition

I am currently completing the More JOIN operations tutorial of sqlzoo and encountered the following code as the answer to #12: 

SELECT yr,COUNT(title) 
FROM movie 
JOIN casting 
JOIN actor   
ON actorid=actor.id AND movie.id=movieid
WHERE name='John Travolta'
GROUP BY yr

HAVING COUNT(title)=
(SELECT MAX(c) FROM
(SELECT yr, COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)

Is there not a more concise way to express this code?

Upvotes: 1

Views: 256

Answers (2)

Megan Squire
Megan Squire

Reputation: 1010

It looks like the original site's suggested answer does not match the question they were asking.

Best answer to what they asked:

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)>2

Their answer was:

SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=
(SELECT MAX(c) FROM (SELECT yr,COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t

)

But this actually answers a different question, which is: "For the year in which he made the most movies, what year was that and how many movies did he make?"

Upvotes: 1

Karl Kieninger
Karl Kieninger

Reputation: 9149

Yes. If I understand the quesiton correctly it is much more simple.

SELECT yr,COUNT(title) 
   FROM movie JOIN casting ON movie.id=movieid
        JOIN 
        actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title) > 2

Upvotes: 2

Related Questions