Re: [sqlite] combining multiple queries
On 12/15/06, jason schleifer <[EMAIL PROTECTED]> wrote: If the user specifies one or more tags, I want to be able to return all moves that match EITHER tag.. for example, if htey specify "carrey" and "snow", it should return all movies that have either carrey or snow in the list. maybe this might help? select * from yourtable where tag = 'carrey' union select * from yourtable where tag = 'snow' etc... -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] combining multiple queries
Hi Jason.
Can make is easier so:
SELECT * FROM film WHERE
genre IN ("comedy", "drama")
AND tag IN ("bill murry", "scarlett o'hara"
,"silly");
--- jason schleifer <[EMAIL PROTECTED]> wrote:
> heya folks,
>
> I'm trying to figure out the proper syntax for doing
> a query of something
> like this:
>
> I have two different types of things that people
> will be querying for, and I
> want to do an OR on each, but and AND between them..
>
> So let's say i've got a table that looks like this:
>
> Create TABLE film (id INTEGER PRIMARY KEY, genre
> VARCHAR, tag VARCHAR):
>
> Genre can be of whatever genre the user wants..
> comedy, drama, etc.
> tag is a list of words that are separated by ;..
> "kennedy;marilyn;funny;"
> etc
>
> If the user doesn't specify either genre or a tag
> when they're querying, I
> want to return the whole list of films (that's an
> easy one).
>
> If the user specifies one or more tags, I want to be
> able to return all
> moves that match EITHER tag.. for example, if htey
> specify "carrey" and
> "snow", it should return all movies that have either
> carrey or snow in the
> list.
>
> If they then specify a genre, I want it to return
> ONLY the movies in the
> specified genre that match carrey or snow.
>
> Then (just to make it more complicated), if they
> specify two genres, it
> should return all movies in either of those genres
> that match either carrey
> or snow.
>
> Does this make sense?
>
> I know how to string a whole bunch of LIKEs together
> with an OR to get it to
> work so you could get it to match a genre or a tag..
> but how do I do
> something like:
>
> SELECT * FROM film WHERE (genre = "comedy" OR genre
> = "drama") AND (tag =
> "bill murry" OR tag = "scarlett o'hara" OR tag =
> "silly");
>
> any help is greatly appreciated..
>
> cheers!
> -jason
> --
> jason schleifer
> ah-ni-may-tor | weirdo
> http://jonhandhisdog.com/
>
Yahoo!?
Yahoo! !
http://ru.mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] combining multiple queries
two tings I would do.
1. further normalize your tag field make it a table keyed by id and tag
join to this in your query
2. for genre = "comedy" OR genre = "drama" I would genre IN ("comedy",
"drama")
you might consider further normalizing genre, to reduce duplication and
also allow multiple genre for a film.
jason schleifer wrote:
heya folks,
I'm trying to figure out the proper syntax for doing a query of something
like this:
I have two different types of things that people will be querying for,
and I
want to do an OR on each, but and AND between them..
So let's say i've got a table that looks like this:
Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR):
Genre can be of whatever genre the user wants.. comedy, drama, etc.
tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;"
etc
If the user doesn't specify either genre or a tag when they're
querying, I
want to return the whole list of films (that's an easy one).
If the user specifies one or more tags, I want to be able to return all
moves that match EITHER tag.. for example, if htey specify "carrey" and
"snow", it should return all movies that have either carrey or snow in
the
list.
If they then specify a genre, I want it to return ONLY the movies in the
specified genre that match carrey or snow.
Then (just to make it more complicated), if they specify two genres, it
should return all movies in either of those genres that match either
carrey
or snow.
Does this make sense?
I know how to string a whole bunch of LIKEs together with an OR to get
it to
work so you could get it to match a genre or a tag.. but how do I do
something like:
SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag =
"bill murry" OR tag = "scarlett o'hara" OR tag = "silly");
any help is greatly appreciated..
cheers!
-jason
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] combining multiple queries
heya folks, I'm trying to figure out the proper syntax for doing a query of something like this: I have two different types of things that people will be querying for, and I want to do an OR on each, but and AND between them.. So let's say i've got a table that looks like this: Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR): Genre can be of whatever genre the user wants.. comedy, drama, etc. tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;" etc If the user doesn't specify either genre or a tag when they're querying, I want to return the whole list of films (that's an easy one). If the user specifies one or more tags, I want to be able to return all moves that match EITHER tag.. for example, if htey specify "carrey" and "snow", it should return all movies that have either carrey or snow in the list. If they then specify a genre, I want it to return ONLY the movies in the specified genre that match carrey or snow. Then (just to make it more complicated), if they specify two genres, it should return all movies in either of those genres that match either carrey or snow. Does this make sense? I know how to string a whole bunch of LIKEs together with an OR to get it to work so you could get it to match a genre or a tag.. but how do I do something like: SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag = "bill murry" OR tag = "scarlett o'hara" OR tag = "silly"); any help is greatly appreciated.. cheers! -jason -- jason schleifer ah-ni-may-tor | weirdo http://jonhandhisdog.com/
