CODEWITHSUNDEEP
sql
whymario
whymario

Reputation: 1

sql get a unique ID then count the number of tuples relating to that ID

Database Structure

MovieInfo (mvID, title, rating, year, length, studio)
DirectorInfo(directorID, firstname, lastname)
MemberInfo(username, email, password)
ActorInfo(actorID, firstname, lastname, gender, birthplace)
CastInfo(mvID*, actorID*)
DirectInfo(mvID*, directorID*)
GenreInfo(mvID*, genre)
RankingInfo(username*, mvID*, score, voteDate)

Query

I need to get the director with the largest number of comedy movies. (I'm also required to use the ALL operator). My understanding is getting the list of mvid where genre = 'Comedy" and directorid:

select mvid
from genreinfo
where genre = 'Comedy'
union all
select directorid
from directorinfo
;

But then how do I count the number of movies a specific director has? And how do I get that single one with the highest count of "comedy" movies?

Upvotes: 0

Views: 153

Answers (4)

rink.attendant.6
rink.attendant.6

Reputation: 46267

You're on the right track. I'd recommend looking at JOINs.

I've provided a step-by-step answer on how to obtain the desired results. If you just want the final query, go down to step 5 and pick the one appropriate for your DBMS.

1: Selecting all comedy movie IDs:

SELECT mvid
FROM GenreInfo
WHERE genre = 'Comedy';

2: Selecting the directorIDs of those movies

SELECT directorID
FROM DirectInfo
JOIN GenreInfo
ON DirectInfo.mvID = GenreInfo.mvID
WHERE genre = 'Comedy';

3: Selecting the director name of those directors.

SELECT firstname
FROM DirectorInfo
JOIN DirectInfo
ON DirectorInfo.directorID = DirectInfo.directorID
JOIN GenreInfo
ON DirectInfo.mvID = GenreInfo.mvID
WHERE genre = 'Comedy';

4: Grouping that query by director to get number of movies:

SELECT firstname, COUNT(*) AS NumberOfMovies
FROM DirectorInfo
JOIN DirectInfo
ON DirectorInfo.directorID = DirectInfo.directorID
JOIN GenreInfo
ON DirectInfo.mvID = GenreInfo.mvID
WHERE genre = 'Comedy'
GROUP BY DirectorInfo.directorID;

5: Sort the results and get only the first one:

SELECT firstname, COUNT(*) AS NumberOfMovies
FROM DirectorInfo
JOIN DirectInfo
ON DirectorInfo.directorID = DirectInfo.directorID
JOIN GenreInfo
ON DirectInfo.mvID = GenreInfo.mvID
WHERE genre = 'Comedy'
GROUP BY DirectorInfo.directorID
ORDER BY NumberOfMovies
LIMIT 1;

If you're using SQL server, use TOP instead:

SELECT TOP 1 firstname, COUNT(*) AS NumberOfMovies
FROM DirectorInfo
JOIN DirectInfo
ON DirectorInfo.directorID = DirectInfo.directorID
JOIN GenreInfo
ON DirectInfo.mvID = GenreInfo.mvID
WHERE genre = 'Comedy'
GROUP BY DirectorInfo.directorID
ORDER BY NumberOfMovies;

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

This is homework? Well, right now you are selecting a list of IDs, some of them representing directors, others representing movies. You notice that this is not at all what you are supposed to do, right?

What you want is a list of directors. So you select from the DirectorInfo table. You also want information about his movies (excatly: the number of movies of a certain kind). So you must join that information from MovieInfo. Now think about what else you need to glue together to get from director to their movies. Then think about how to glue in that genre criterium.

Once you have joined it all together, then you group your results. You want one record per director (instead of ane record per director and movie), so you make a group and count within that group.

I hope this helps you solve your task. Good luck!

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

select di.directorid, count(1) as 'no_of_comedy_movies'
from   DirectorInfo di inner join join DirectInfo dri
on     di.directorid = dri.directorid
       inner join genreinfo gi
on     gi.mvid = dri.mvid
where  gi.genre = 'Comedy'
group by dri.directorID
order by no_of_comedy_movies

Upvotes: -1

Chamal
Chamal

Reputation: 1449

You can use a join and group by to get the result.

select DirectorID,COUNT(mvid)
from DirectInfo d
inner join genreinfo g
ON d.mvid=g.mvid
where genre ='Comedy'
GROUP BY DirectorID
ORDER BY COUNT(mvid)

Upvotes: 0

Related Questions