Media List Editor Examples
#76
(2020-04-08, 11:37)TheGeekSteve Wrote: 1. Movies with only a German audio stream
I'm not shure if that is the best/fastest way to query, but it works. Maybe an SQL expert know a better way to solve it. This query does:
  1. get a list of all "MovieID" that have more than one entry (stream) in the MoviesAStreams
  2. use that ID list to filter the full "movielist" by movies with an ID that's in that ID list and also have the audio language "deu" or "ger"
SELECT DISTINCT *
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
WHERE idMovie IN (
SELECT idMovie
FROM
  movielist
  INNER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING
  COUNT(MoviesAStreams.StreamID) = 1) AND
(MoviesAStreams.Audio_Language = 'deu' OR MoviesAStreams.Audio_Language = 'ger')
 
(2020-04-08, 11:37)TheGeekSteve Wrote: 2. Movies with a German AND a English (or any other language) audio stream
Same syntax but with more than one stream (COUNT(MoviesAStreams.StreamID) > 1):
SELECT DISTINCT *
FROM
  movielist
  INNER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
WHERE idMovie IN (
SELECT idMovie
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING
  COUNT(MoviesAStreams.StreamID) > 1) AND
(MoviesAStreams.Audio_Language = 'deu' OR MoviesAStreams.Audio_Language = 'ger')
 
(2020-04-08, 11:37)TheGeekSteve Wrote: 3. Movies with only 1 audio stream
SELECT DISTINCT *
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING
  COUNT(MoviesAStreams.StreamID) = 1
 
(2020-04-08, 11:37)TheGeekSteve Wrote: 4. Movies with more than 1 audio stream
SELECT DISTINCT *
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING
  COUNT(MoviesAStreams.StreamID) > 1
Reply
#77
(2020-04-08, 12:24)DanCooper Wrote: SELECT DISTINCT *FROM movielist
LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)WHERE idMovie IN (SELECT idMovieFROM movielist
INNER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)GROUP BY movielist.idMovieHAVING COUNT(MoviesAStreams.StreamID) = 1) AND(MoviesAStreams.Audio_Language = 'deu' OR MoviesAStreams.Audio_Language = 'ger')

That's great. Thank you so much!

I have one more that I forgot to mention:

Movies where the language of the audio stream is not defined.
Reply
#78
(2020-04-08, 13:16)TheGeekSteve Wrote: Movies where the language of the audio stream is not defined.
Hint: that are two apostrophe (') and not quotation marks (")!

SELECT DISTINCT *
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
WHERE
  MoviesAStreams.Audio_Language = ''
Reply
#79
I was hoping find an example to find duplicate movies by name, not by IMDB ID, I believe we can use a media list editor right? If yes I cant find one here when I search this with "duplicate"...
Reply



Logout Mark Read Team Forum Stats Members Help
Media List Editor Examples1
This forum uses Lukasz Tkacz MyBB addons.