CODEWITHSUNDEEP
sqlsql-server
k80sg
k80sg

Reputation: 2473

SQL Join Query help

I am doing some SQL exercise and having this problem, this query below gives me a 'half correct' result because I only want the row(s) with the most title to be displayed, this query is displaying all records. Can someone help? Thanks.

Question:

Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

Tables:

Query:

select yr, max(title)
from 
(
select yr, count(title) title from movie
join casting
on (movie.id=casting.movieid)
join actor
on (casting.actorid=actor.id)
where actor.name="John Travolta"
group by yr Asc
) a

Upvotes: 0

Views: 842

Answers (7)

Sarth
Sarth

Reputation: 266

Here is an easier solution with explanation-

  1. First we make a join of all the tables
  2. Then we put a category filter on name with name= 'John Travolta'
  3. Now we put a group function on yr so that we have yr and corresponding count(yr)

  4. Solution wants to have years with count>2 only so we apply that filter on grouped data by using 'having' clause

    select yr, count(yr) from movie join casting on movie.id=movieid join actor on actor.id=actorid where name= 'John Travolta' group by yr having count(yr)>2

Hope this helps, I dont see a need to write a Procedure for this.

Upvotes: 0

Abhishek Bansal
Abhishek Bansal

Reputation: 51

This a simple answer using both join and sub-query concept

select yr,count(title) from movie inner join casting on movie.id=casting.movieid where actorid= (select id from actor where name ='John Travolta') group by yr having count(title)>2

Upvotes: 0

huzaifa jasnak
huzaifa jasnak

Reputation: 23

This is much easy solution.

select yr, count(yr)  
from movie 
join casting on movie.id = movieid
join actor on actorid = actor.id
where name = 'John Travolta' 
group by yr
having count(yr) > 2**

Happy to help

Upvotes: 1

mylight
mylight

Reputation: 11

the answer is:

SELECT y.yr,MAX(y.count)
FROM(
SELECT movie.yr,COUNT(movie.yr) AS count
FROM (movie JOIN casting ON (movie.id=movieid)) JOIN actor ON (actor.id=actorid)
WHERE name='John Travolta'
GROUP BY yr
ORDER BY COUNT(movie.yr) DESC) y

Upvotes: 1

canon
canon

Reputation: 41715

The question asks

Which were the busiest years?

... plural. So, what were his top 5 years?

select top 5
 m.yr
,count(*)
from actor as a
    join casting as c
        join movie as m
        on m.movieid = c.movieid
    on c.actorid = a.actorid
where a.name = 'John Travolta'
group by
 m.yr
order by
 count(*) desc

However, the second part of the question specifies that you should

Show the number of movies he made for each year.

So far our query doesn't account for years in which John made no movies... so, this might be where your half correct comes into play. That said, you may want to create a table variable filled with year values from 1954 through the current year... and left join off of that.

declare @year table
(
    [yr] int
)
declare @currentYear int = datepart(year,getdate())
while @currentYear >= 1954 begin -- Travolta was born in 1954!

    insert @year values (@currentYear)
    set @currentYear -= 1

end

select
 y.yr
,count(m.movieid)
from @year y
    left join movies as m
        join casting as c
            join actor as a
            on a.actorid = c.actorid
            and a.name = 'John Travolta'
        on c.movieid = m.movieid
    on m.yr = y.yr
group by
 y.yr
order by
,count(m.movieid) desc

[Edit: based on comments] And a final query to return all years whose count matches the highest of any year.

;with TravoltaMovies as
(
    select
     m.yr
    ,count(*) as [Count]
    from actor as a
        join casting as c
            join movie as m
            on m.movieid = c.movieid
        on c.actorid = a.actorid
    where a.name = 'John Travolta'
    group by m.yr
)
select
*
from TravoltaMovies as tm
where tm.[Count] = (select max([Count]) from TravoltaMovies)

Upvotes: 3

k80sg
k80sg

Reputation: 2473

Thanks all this is the query:

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
)

Upvotes: 0

JNK
JNK

Reputation: 65207

select TOP 1 yr, title
from 
(
select yr, count(title) title from movie
join casting
on (movie.id=casting.movieid)
join actor
on (casting.actorid=actor.id)
where actor.name="John Travolta"
group by yr Asc
) a
ORDER BY title DESC

Just add a TOP selection and an ORDER BY.

The aggregation is unnecessary.

Upvotes: 0

Related Questions