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/
