Find all the actors that made more movies with Yash Chopra than any other director

Scehma

    SELECT   p1.pid, 
         p1.NAME, 
         Count(movie.mid) AS movieswithyc 
FROM     person           AS p1 natural 
JOIN     m_cast natural 
JOIN     movie 
JOIN     m_director 
ON       ( 
                  movie.mid = m_director.mid) 
JOIN     person AS p2 
ON       ( 
                  m_director.pid = p2.pid) 
WHERE    p2.NAME LIKE 'Yash Chopra' 
GROUP BY p1.pid 
HAVING   Count(movie.mid) >ALL 
         ( 
                    SELECT     Count(movie.mid) 
                    FROM       person AS p3 natural 
                    JOIN       m_cast 
                    INNER JOIN movie 
                    JOIN       m_director 
                    ON         ( 
                                          movie.mid = m_director.mid) 
                    JOIN       person AS p4 
                    ON         ( 
                                          m_director.pid = p4.pid) 
                    where      p1.pid = p3.pid 
                    AND        p4.NAME NOT LIKE 'Yash Chopra' 
                    GROUP BY   p4.pid) 
ORDER BY movieswithyc DESC;

I’m not getting the right output. I’m getting zero rows . Can someone modify above query and give me the right output, I have tried various queries but not getting anything

Answer

Check this:

SELECT first.actor, 
       first.count 
FROM   (SELECT Trim(actor) AS Actor, 
               Count(*)    AS COUNT 
        FROM   m_cast mc 
               INNER JOIN (SELECT m.mid 
                           FROM   movie m) AS m 
                       ON m.mid = Trim(mc.mid) 
               INNER JOIN (SELECT md.pid, 
                                  md.mid 
                           FROM   m_director md) AS md 
                       ON md.mid = Trim(mc.mid) 
               INNER JOIN (SELECT p.pid, 
                                  p.NAME AS actor 
                           FROM   person p) AS pactor 
                       ON pactor.pid = Trim(mc.pid) 
               INNER JOIN (SELECT p.pid, 
                                  p.NAME AS director 
                           FROM   person p) AS pdirector 
                       ON pdirector.pid = Trim(md.pid) 
        WHERE  director LIKE '%Yash Chopra%' 
        GROUP  BY Trim(actor)) first 
       LEFT JOIN (SELECT actor, 
                         Max(count) AS COUNT 
                  FROM   (SELECT DISTINCT Trim(actor) AS Actor, 
                                          Count(*)    AS COUNT 
                          FROM   m_cast mc 
                                 INNER JOIN (SELECT m.mid 
                                             FROM   movie m) AS m 
                                         ON m.mid = Trim(mc.mid) 
                                 INNER JOIN (SELECT md.pid, 
                                                    md.mid 
                                             FROM   m_director md) AS md 
                                         ON md.mid = Trim(mc.mid) 
                                 INNER JOIN (SELECT p.pid, 
                                                    p.NAME AS actor 
                                             FROM   person p) AS pactor 
                                         ON pactor.pid = Trim(mc.pid) 
                                 INNER JOIN (SELECT p.pid, 
                                                    p.NAME AS director 
                                             FROM   person p) AS pdirector 
                                         ON pdirector.pid = Trim(md.pid) 
                          WHERE  director NOT LIKE '%Yash Chopra%' 
                          GROUP  BY Trim(actor), 
                                    director) 
                  GROUP  BY actor) second 
              ON first.actor = second.actor 
WHERE  first.count >= second.count 
        OR second.actor IS NULL 
ORDER  BY first.count DESC