Reputation: 47
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
Upvotes: 0
Views: 3204
Answers (7)
Reputation: 1
Hey folks who are new to sql and trying very hard to solve this question like me, you can find the part of a solution(99%) below, as i don't want to interupt your process of learning. But before going through it, try for one last time. I am thankful to the people who have discussed their various thoughts on this question in this forum, as they have triggered various ideas in me.
Before going through the solution you can have a look at this video to get an overview on various new keywords used in the below code. disclaimer - use trim option wherever required
select actor,movies from
( select mc.pid as actor,
md.pid as director,
p.pid,
count(*) as movies,
rank() over (partition by mc.pid order by count(*) desc) as rn,
p.name
from m_director as md
join
m_cast as mc on md.mid=mc.mid
left join
person as p on md.pid=p.pid and name = 'Yash Chopra'
group by mc.pid,md.pid
)
where rn =1 and director like "nm0007181" ;
exact solution - in order to get the exact solution you can join the above table with people table to get the names of actors who had been directed more by yash chopra than any other director.
paila saisravan - data digger
Upvotes: 0
Reputation: 21
select t.actor,t.count from ( SELECT actor,count(distinct m.mid) 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%'
--and actor like '%Uttam Sodi%'
group by actor) as t
join( SELECT actor,count(distinct m.mid) 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 actor) as w
where t.actor=w.actor and t.count>=w.count
Upvotes: 0
Reputation: 445
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
Upvotes: 3
Reputation: 1
select p.name,h.count
from(select mc.pid as mcpid,md.pid as mdpid,count(mc.MID) as count
from m_cast as mc
join m_director md
on md.MID=mc.MID
group by mc.pid ,md.pid
) h
join person p
on h.mcpid=p.pid
where h.count = (select count(*) as count
from m_cast as mc
join m_director md
on md.mid=mc.mid
where mc.pid=h.mcpid
group by mc.pid,md.pid
order by count(*) desc
limit 1)
and h.mdpid = (select pid
from person
where name like '%Yash Chopra%'
)
order by h.count desc
Upvotes: -1
Reputation: 1
p2.NAME LIKE 'Yash Chopra' and p1.PID This is your line from the code. You should have written it like this TRIM(p2.NAME),TRIM(p1.PID) because the Name and PID from your Movie Table contains spaces and things like that.You should process it correctly else it will return zero rows, keep that thing in mind.
Upvotes: 0
Reputation: 47
SELECT *
FROM (
SELECT pc.NAME,
Count(DISTINCT Trim(m.mid)) count_movie
FROM movie m
JOIN m_cast mc
ON Trim(m.mid) = Trim(mc.mid)
JOIN m_director md
ON Trim(m.mid) = Trim(md.mid)
JOIN person pc
ON Trim(mc.pid) = Trim(pc.pid)
JOIN person pd
ON trim(md.pid )= Trim(pd.pid) where pd.NAME = 'Yash Chopra' GROUP BY pc.NAME) lst_yc
JOIN
(
SELECT pc.NAME,
count(trim(m.mid)) count_movie
FROM movie m
JOIN m_cast mc
ON trim(m.mid) = trim(mc.mid )
JOIN m_director md
ON trim(m.mid) = (md.mid)
JOIN person pc
ON trim(mc.pid) = trim(pc.pid)
JOIN person pd
ON trim(md.pid) = trim(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
This seems to be the answer as given by Mr. Shantanu. But Do you know why this is taking time, I ran query 1 hour ago and no reult has produced yet.
Upvotes: 0
Reputation: 1054
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'.
(select pc.name, count(distinct 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_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
Upvotes: 0
Related Questions
- Write a query to find the full name of the actor who has acted in the third most number of movies in mysql?
- SQL Excercise (movies with number of actors)
- SQL query: To find an actors who did more films with Quentin Tarantino
- SQL Query not showing results. The query is to list the names of all the actors who played in the movie 'Anand'
- Get all actors who have played in every film categor at least twice (Sakila DB)
- mysql query to find actors acted in both films
- SQL select the actor and movie name from every actor that has worked with a particular actor
- How to list the amount of movies each actor has starred in
- Display the title and year of all non SH and non CH films that were produced in the same year as any of the SH or CH videos were produced
- Display the title and year for all of the films that were produced in the same year as any of the SH and CH films