Re: [sqlite] combining multiple queries

[Artem Yankovskiy]

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/
> 


&#1042;&#1099; &#1091;&#1078;&#1077; &#1089; Yahoo!? 
&#1048;&#1089;&#1087;&#1099;&#1090;&#1072;&#1081;&#1090;&#1077; 
&#1086;&#1073;&#1085;&#1086;&#1074;&#1083;&#1077;&#1085;&#1085;&#1091;&#1102; 
&#1080; &#1091;&#1083;&#1091;&#1095;&#1096;&#1077;&#1085;&#1085;&#1091;&#1102; 
Yahoo! &#1055;&#1086;&#1095;&#1090;&#1091;! 
http://ru.mail.yahoo.com 

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------