[DanCooper]

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"
   

sql:
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')

 

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):

sql:
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')

 

3. Movies with only 1 audio stream

sql:
SELECT DISTINCT *

FROM

  movielist

  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)

GROUP BY

  movielist.idMovie

HAVING

  COUNT(MoviesAStreams.StreamID) = 1

 

4. Movies with more than 1 audio stream

sql:
SELECT DISTINCT *

FROM

  movielist

  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)

GROUP BY

  movielist.idMovie

HAVING

  COUNT(MoviesAStreams.StreamID) > 1

[TheGeekSteve]

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.

[DanCooper]

Movies where the language of the audio stream is not defined.

Hint: that are two apostrophe (') and not quotation marks (")!

sql:
SELECT DISTINCT *

FROM

  movielist

  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)

WHERE

  MoviesAStreams.Audio_Language = ''

[geek-baba]

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"...

[ping182nz]

Can i please have some help? I would like a query that lists only UHD/4K movies.... what would be the best way to do that?

[ping182nz]

Can i please have some help? I would like a query that lists only UHD/4K movies.... what would be the best way to do that?

Is the above possible?

[DanCooper]

Can i please have some help? I would like a query that lists only UHD/4K movies.... what would be the best way to do that?

sql:
SELECT DISTINCT

  movielist.*

FROM

  movielist

  LEFT OUTER JOIN MoviesVStreams ON (MoviesVStreams.MovieID = movielist.idMovie)

WHERE

  MoviesVStreams.Video_Width = 3840 OR

  MoviesVStreams.Video_Width = 4096

[ping182nz]

Thank you very much!

[User 82367]

How can i get all movies with an empty trailer path? Thanks!

Edit: Ok ignore it, the default data filter with "is empty" and "trailer" works.

[Anderella]

Hi,
as I can't figure out the correct naming of the audiotrack-field, I need help:

I would like a movie filter where the result shows all movies with audiotrack 0 is not Deu or German.

Thx for help.

[DanCooper]

Hi,
as I can't figure out the correct naming of the audiotrack-field, I need help:

I would like a movie filter where the result shows all movies with audiotrack 0 is not Deu or German.

Thx for help.

I tried to answer your question but got a security message from CloudFlare:



It was a default SQL query... no idea why they block that. So you can ask the same question in the german forum and I will try to answere it there.

[Ericbwindsor]

How to create filters where movies are in the 80s, 90s etc?

[DanCooper]

There is a filter panel down below the list where you can filter the years "from -  to".

[Ericbwindsor]

where exactly is the media list editor?  i tried copying both modules and profiles folder to another computer but it looks like that's not it.

[DanCooper]

The "Media List Editor" is a module stored under .\Ember Media Manager\Modules\generic.embercore.medialisteditor and, like all other modules, should never copied from one installation to another because all modules are heavily depended to the Ember Media Manager API class.
The Media List Editor creates new "views" in the main database .\Ember Media Manager\Profiles\MyVideos##.emm, so it's also not needed to copy the module to save or copy any custom view from one datebase or profile to another one.