Thread Rating:
  • 1 Vote(s) - 5 Average
Media List Editor Examples
#1
Information 
Please use this schema to post your SQLite example queries:



Movies with more than one audio stream
Type: movie
Query:
Code:
SELECT
  movielist.*
FROM
  movielist
  LEFT OUTER JOIN MoviesAStreams ON (movielist.idMovie = MoviesAStreams.MovieID)
GROUP BY
  movielist.idMovie
HAVING COUNT(MoviesAStreams.MovieID) > 1
Reply
#2
All 720p movies
Type: movie
Name: 720p movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE MoviesVStreams.Video_Width = '1280'

All FullHD movies
Type: movie
Name: 1080p movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE MoviesVStreams.Video_Width = '1920'

All non-HD movies
Type: movie
Name: not HD movies
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie) WHERE (MoviesVStreams.Video_Width != '1920' AND MoviesVStreams.Video_Width != '1280')

Anime movies
Type: movie
Name: Anime movies
Query:
Code:
SELECT * FROM movielist WHERE (Genre LIKE '%Animation%') AND (Country LIKE '%Japan%')

Movies with best audio quality
Type: movie
Name: Best Audio
Query:
Code:
SELECT DISTINCT movielist.* FROM MoviesAStreams INNER JOIN movielist ON (MoviesAStreams.MovieID  = movielist.idMovie) WHERE (MoviesAStreams.Audio_Codec = 'dtshd_hra' OR MoviesAStreams.Audio_Codec = 'dtshd_ma' OR MoviesAStreams.Audio_Codec = 'truehd')
Reply
#3
This coding is well beyond my capability. If someone wouldn't mind id love an example of how to do this for a specific source. Then I could separate my workout, documentaries, kids shows etc.. From my movies. If not possible then a specific genre or tag might work.
Reply
#4
(2015-11-27, 08:38)beesmyer Wrote: This coding is well beyond my capability. If someone wouldn't mind id love an example of how to do this for a specific source. Then I could separate my workout, documentaries, kids shows etc.. From my movies. If not possible then a specific genre or tag might work.

Example for a source called "Dokus":

Code:
SELECT
  movielist.*
FROM
  movielist
  INNER JOIN moviesource ON (movielist.idSource = moviesource.idSource)
WHERE
  moviesource.strName = "Dokus"
Reply
#5
Dan, Thank you for the help. I get an error when entering this. Can't figure out what I might be doing wrong. Any ideas? Or anything else I can give you to tell you the error?
Reply
#6
There is a log file inside the log folder. Please upload it as zip or something else.

Edit: Can you tell me your database version? .\EmberMEdiaManager\Settings\MyVideo??.emm
Maybe the moviesource table calls "Sources" in your version.
Reply
#7
MyVideos24

I'm searching rules and figuring out uploading log files right now since I haven't done it before
Reply
#8
Ok, in version 24 the movie source table is called "Sources", also the columns has other names. Please try this SQL:

Code:
SELECT
  movielist.*
FROM
  movielist
  INNER JOIN Sources ON (movielist.Source = Sources.Name)
WHERE
  Sources.Name = "Dokus"
Reply
#9
That disables the "add" button.

Honestly. I don't want this to take up much of your time. I can make do without and would much prefer some of the other work to get done. I know how much time it takes.
Reply
#10
(2015-11-29, 03:13)beesmyer Wrote: That disables the "add" button.

Honestly. I don't want this to take up much of your time. I can make do without and would much prefer some of the other work to get done. I know how much time it takes.

Oh, i think we have a little savety-check that does not work with newline between "SELECT" and "movielist".
Same code, but this should be working:

Code:
SELECT movielist.*
FROM movielist INNER JOIN Sources ON (movielist.Source = Sources.Name)
WHERE Sources.Name = "Dokus"
Reply
#11
That worked. Thank you. Now I'll try leave you alone so we can get the new release.
Reply
#12
I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes? Again, I don't know what I'm doing, but would this work:

Code:
SELECT DISTINCT movielist.* FROM MoviesVCodecs INNER JOIN movielist ON (MoviesVCodecs.MovieID  = movielist.idMovie) WHERE MoviesVCodecs = 'hevc'

I just changed what looked like what needed to be changed, but I don't know this scripting Sad
Reply
#13
(2016-04-14, 04:20)Fail$tyle420 Wrote: I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes?

Code:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)
WHERE
  MoviesVStreams.Video_Codec = 'hevc'
Reply
#14
(2016-04-14, 10:09)DanCooper Wrote:
(2016-04-14, 04:20)Fail$tyle420 Wrote: I am just now getting around to playing with this and it's AWESOME! Unfortunately, I'm in the same boat as beesmyer....but I was able to get all of the examples given already working! My question is, how would I go about gaining a list that only shows hevc encodes?

Code:
SELECT DISTINCT
  movielist.*
FROM
  movielist
  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)
WHERE
  MoviesVStreams.Video_Codec = 'hevc'

Beautiful, thank you! I'll give this a go!
Reply
#15
EMM does not store the resolution of art files (poster, thumbs, fanart, etc), correct?
Reply



Media List Editor Examples51