[User 82367]

can someone help me with some querys?

1. find movie plots with specific language (or maybe find a word like "the" in the plot?)

2. find movies with more than 3 genres

3. find movies with video bitrate < 10000

4. find movies with filesize > 10000 mb

[DanCooper]

can someone help me with some querys?

1. find movie plots with specific language (or maybe find a word like "the" in the plot?)

2. find movies with more than 3 genres

3. find movies with video bitrate < 10000

4. find movies with filesize > 10000 mb

1.

sql:
SELECT DISTINCT * FROM movielist WHERE Plot LIKE '%the%'

2.

sql:
SELECT DISTINCT

  movielist.*

FROM

  genre

  LEFT OUTER JOIN genrelinkmovie ON (genre.idGenre = genrelinkmovie.idGenre)

  LEFT OUTER JOIN movielist ON (genrelinkmovie.idMovie = movielist.idMovie)

GROUP BY

  movielist.idMovie

HAVING

  COUNT(genre.idGenre) > 3

3.

sql:
SELECT DISTINCT

  movielist.*

FROM

  movielist

  INNER JOIN MoviesVStreams ON (movielist.idMovie = MoviesVStreams.MovieID)

WHERE

  MoviesVStreams.Video_Bitrate < 10000

4. not possible, filesize is not stored to the database

[DanCooper]

Movies with chinese subtitles
Database version: MyVideos46.emm
Type: movie
Query:

sql:
SELECT DISTINCT

  movielist.*

FROM

  movielist

  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)

WHERE

  MoviesSubs.Subs_LongLanguage LIKE 'chinese'

Movies with chinese embedded subtitles
Database version: MyVideos46.emm
Type: movie
Query:

sql:
SELECT DISTINCT

  movielist.*

FROM

  movielist

  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)

WHERE

  MoviesSubs.Subs_LongLanguage LIKE 'chinese' AND

  MoviesSubs.Subs_Type = 'Embedded'

Movies with chinese external subtitles (files)
Database version: MyVideos46.emm
Type: movie
Query:

sql:
SELECT DISTINCT

  movielist.*

FROM

  movielist

  INNER JOIN MoviesSubs ON (movielist.idMovie = MoviesSubs.MovieID)

WHERE

  MoviesSubs.Subs_LongLanguage LIKE 'chinese' AND

  MoviesSubs.Subs_Type = 'External'

[badbob001]

I made a simple modification to an example query to find all non-HD movies but it doesn't seem to work:

sql:
SELECT DISTINCT movielist.* FROM MoviesVStreams INNER JOIN movielist ON (MoviesVStreams.MovieID  = movielist.idMovie)

WHERE (MoviesVStreams.Video_Width < 900)

Is MoviesVStreams.Video_Width a string type and not a number type, which is why the examples use quotes around the number (eg: '1280', '1080').

[badbob001]

Fix: ...WHERE (CAST (MoviesVStreams.Video_Width as UNSIGNED int) < 900)

[redglory]

@DanCooper how can I delete some custom media query lists.

[DanCooper]

@DanCooper how can I delete some custom media query lists.

Select it in the settings upper dropdown list and use the remove button:

[redglory]

@DanCooper how can I delete some custom media query lists.

Select it in the settings upper dropdown list and use the remove button:

Hi Dan mine is greyed out when I select one on the list.

Thx

[DanCooper]

Hi Dan mine is greyed out when I select one on the list.

Thx

Hmmm, i think the button is disabled if the "title" field is empty or the SQL query is wrong. Try this:

1. select the list in the dropdown menu
   
2. fill a title if empty
   
3. use "SELECT * FROM movielist" as query, than the remove button should be enabled
   

[redglory]

Hi Dan mine is greyed out when I select one on the list.

Thx

Hmmm, i think the button is disabled if the "title" field is empty or the SQL query is wrong. Try this:

1. select the list in the dropdown menu
   
2. fill a title if empty
   
3. use "SELECT * FROM movielist" as query, than the remove button should be enabled
   

 

Had already tried every combination. Still greyed out


EDIT: tried to select sets on type added pt onde the title and remove appeared. Clicked it and it removed my saved query.

A little weird but it worked.

EDIT2: It makes sense. Titles are preceeded with type so you only have to put the title after the hypen for it to be selected.

[geek-baba]

Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?

[DanCooper]

Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?

sql:
SELECT *

FROM

  movielist

WHERE

  MPAA LIKE 'R%' OR

  MPAA LIKE 'NC-17%'

[geek-baba]

Hi - is the mppa rating saved in database and how to create list with R or NC-17 rating if possible?

sql:
SELECT *

FROM

  movielist

WHERE

  MPAA LIKE 'R%' OR

  MPAA LIKE 'NC-17%'

 

Thank you! Figured certification field works better, because MPPA field has "Rated R" so above query will fetch almost 99% of the movies.

sql:
SELECT *

FROM

  movielist

WHERE

  Certification LIKE 'USA:R%'

[DanCooper]

Thank you! Figured certification field works better, because MPPA field has "Rated R" so above query will fetch almost 99% of the movies.

The % char in the query is the same like * in Windows. That's the reason why "every rating starting with "r" will be listed.

If you one want to search for "Rated R" than you can use

sql:
SELECT * FROM movielist WHERE MPAA LIKE 'Rated R'

or

sql:
SELECT * FROM movielist WHERE Certification LIKE 'USA:R'

[TheGeekSteve]

Hi there looking for some queries concerning audio stream languages. Thanks in advance!

1. Movies with only a German audio stream

2. Movies with a German AND a English (or any other language) audio stream

3. Movies with only 1 audio stream

4. Movies with more than 1 audio stream