SQL: Extracting data from three tables

November 23, 2022

I am looking to show in ascending order the NameOfMovie that occur most often from the MoviePlaylist where the Playlist Genre_Name = TV Shows or 90’s movie.

The three tables are setup as follows:

   Movie => MovieID, NameOfMovie
   PlaylistMovie => MovieID, PlaylistID
   Playlist => PlaylistID, Genre_Name

I have tried the following but it does not work:

Select movie.MovieId, movie.NameOfMovie, playlisttrack.PlaylistId
From playlisttrack
Inner Join movie on movie.MovieId = playlistMovie.MovieId
Inner Join playlist on playlistMovie.playlistId = playlist.playlistId
where playlistMovie.PlaylistId = 'TV Shows' or playlistMovie.PlaylistId = '90s Movie'
group by Movie.Name;

>Solution :

Like this?

select track.Name, 
       count(*) track_count
  from playlisttrack
 inner Join track on track.TrackId = playlisttrack.TrackId
 inner Join playlist on playlisttrack.playlistId = playlist.playlistId
where playlisttrack.PlaylistId = 3 or playlisttrack.PlaylistId = 5
group by track.Name
order by 2 desc;