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

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

This Post Has One Comment

  1. No Fault

    You can check the below SQL.

    Explanation – First inline view returns list of people with count of their movies with ‘Yash Chopra’. Second inline view returns list of people with count of their movies with other directors. At the end, I filter list of those people where count of movies with ‘Yash Chopra’ is greater than ‘other directors

    count_movie
    from movie m
    join m_cast mc on m.mid = mc.mid
    join m_director md on m.mid = md.mid
    join person pc on mc.pid = pc.pid
    join person pd on md.pid = pd.pid
    where pd.name = ‘YASH CHOPRA’
    group by pc.name) lst_yc
    join
    (select pc.name, count(m.mid) count_movie
    from movie m
    join m_cast mc on m.mid = mc.mid
    join m_director md on m.mid = md.mid
    join person pc on mc.pid = pc.pid
    join person pd on md.pid = pd.pid
    where pd.name != ‘YASH CHOPRA’
    group by pc.name) lst_wo
    on lst_yc.name = lst_wo.name
    where lst_yc.count_movie > lst_wo.count_movie

Leave a Reply