Re: [sqlite] I need help with very complex queries

[Igor Tandetnik]

"Yuzem"  wrote in
message news:22446301.p...@talk.nabble.com
> As an example, I have:
> tables:
> movies genres keywords languages countries etc...
>
> movies columns:
> id title year director etc
>
> The others are:
> id "name of the table" (example: id genres)
>
> The id in all the tables are the same column that is the id of the
> movie so that every movie can be many times in all the tables but
> only one time in movies.
>
> What I want to do is something like:
> select ... where genres = Horror and countries = italy and keywords =
> 

select * from movies where
movies.id in (select id from genres where genres='Horror') and
movies.id in (select id from countries where countries='italy') and
movies.id in (select id from keywords where keywords='');

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I need help with very complex queries

[Yuzem]

Jim Wilcoxson wrote:
> 
> For example, in your query you need to add something like:
> and genres.genre = 'drama'.
Yes but if I add that I neither get any result:

sqlite3 movies.db "select movies.id,title,year from
movies,genres,countries,languages,keywords,tags where movies.id = genres.id
and movies.id = countries.id and movies.id = languages.id and movies.id =
keywords.id and movies.id = tags.id and genres like 'horror'"

The same happens if I use genres.genres instead of only genres.


Jim Wilcoxson wrote:
> 
> For this query, only the movies and
> genres tables are needed because you aren't selecting any fields from
> the other tables, from what I can tell.
Yes I know but shouldn't I get all movies with that? I'm getting none...

-- 
View this message in context: 
http://www.nabble.com/I-need-help-with-very-complex-queries-tp22446301p22447156.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I need help with very complex queries

[Jim Wilcoxson]

You have specified how the movies table relates to the other tables,
but you haven't specified any independent selection criteria for any
tables.   For example, in your query you need to add something like:
and genres.genre = 'drama'.  For this query, only the movies and
genres tables are needed because you aren't selecting any fields from
the other tables, from what I can tell.

Running through a few online SQL tutorials will help you get a better
feel for how SQL works.

Jim

On 3/10/09, Yuzem  wrote:
>
> As an example, I have:
> tables:
> movies genres keywords languages countries etc...
>
> movies columns:
> id title year director etc
>
> The others are:
> id "name of the table" (example: id genres)
>
> The id in all the tables are the same column that is the id of the movie so
> that every movie can be many times in all the tables but only one time in
> movies.
>
> What I want to do is something like:
> select ... where genres = Horror and countries = italy and keywords = 
>
> This is what I have:
> sqlite3 movies.db "select movies.id,title,year from
> movies,genres,countries,languages,keywords,tags where movies.id = genres.id
> and movies.id = countries.id and movies.id = languages.id and movies.id =
> keywords.id and movies.id = tags.id"
>
> There is no query and it is giving me nothing...
> Many thanks in advance!
> --
> View this message in context:
> http://www.nabble.com/I-need-help-with-very-complex-queries-tp22446301p22446301.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users