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