title: Homework Question :: Tried very hard :: Need Direction
body: <p>I have been stuck on this progressive question for more than 10 days now</p>
  <p>Questions is :: Find all the actors that made more movies with Yash Chopra than any other director</p>
  <p>Heres my attempt</p>
  <pre><code>SELECT pidsWhoDidMoviesWithYashChopra.pid,
              pidsWhoDidMoviesWithYashChopra.moviesWithYashChopra,
              pidsOfThoseWhoDidMoviesWithDirectors.moviesByAPID,
              pidsWhoDidMoviesWithYashChopra.countOfMoviesWithYashChopraByAPID,
              pidsOfThoseWhoDidMoviesWithDirectors.totalNumberOfMoviesByAPID
              FROM
              (
                  SELECT TRIM(M_Cast.PID) AS pid, moviesByYashChopra.mDirectorMID AS moviesWithYashChopra, COUNT(moviesByYashChopra.mDirectorMID) AS countOfMoviesWithYashChopraByAPID
                  FROM M_Cast
                  JOIN
                  (
                      SELECT TRIM(M_Director.MID) AS mDirectorMID
                      FROM
                      M_Director
                      WHERE TRIM(M_Director.PID) IN
                      (
                          SELECT TRIM(Person.PID) AS personPID
                          FROM
                          Person
                          WHERE Person.Name LIKE '%Yash Chopra%'
                      )
                  ) AS moviesByYashChopra
                  ON TRIM(M_Cast.MID) == moviesByYashChopra.mDirectorMID
                  GROUP BY pid
              ) AS pidsWhoDidMoviesWithYashChopra
              JOIN
              (
                  SELECT TRIM(M_Cast.PID) AS pid, TRIM(M_Cast.MID) AS moviesByAPID, COUNT(TRIM(M_Cast.MID)) AS totalNumberOfMoviesByAPID
                  FROM M_Cast
                  GROUP BY pid
              ) AS pidsOfThoseWhoDidMoviesWithDirectors
              ON pidsWhoDidMoviesWithYashChopra.pid == pidsOfThoseWhoDidMoviesWithDirectors.pid
              GROUP BY pidsWhoDidMoviesWithYashChopra.pid
  </code></pre>
  <p>And here's the output it produces</p>
  <p><a href="https://i.stack.imgur.com/cfUBe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cfUBe.png" alt="enter image description here" /></a></p>
  <p>And here's the schema</p>
  <p><a href="https://i.stack.imgur.com/hNTLq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hNTLq.jpg" alt="enter image description here" /></a></p>
  <p>Now where I require help is :: Ability to go ahead from here :: As in how do i dissect this part of the question &quot;<strong>than any other director.</strong>&quot; :: I think that's the tricky part</p>
  <p>Any direction/hints will be helpful, Thanks...</p>