[DanCooper]

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

[Cocotus]

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

[beesmyer]

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.

[DanCooper]

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"

[beesmyer]

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?

[DanCooper]

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.

[beesmyer]

MyVideos24

I'm searching rules and figuring out uploading log files right now since I haven't done it before

[DanCooper]

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"

[beesmyer]

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.

[DanCooper]

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"

[beesmyer]

That worked. Thank you. Now I'll try leave you alone so we can get the new release.

[Fail$tyle420]

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

[DanCooper]

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'

[Fail$tyle420]

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!

[Daydream]

EMM does not store the resolution of art files (poster, thumbs, fanart, etc), correct?