Hi,

I've been trying to get a particular query to work. I tried subscribing to the SQL lists mentioned on the website, but neither would respond to my subscribe requests. Anyway, I have a database setup as follows:

Table: footage ID, <some other columns>

Table: footage_keywords kID, cID, keyword

How this arrangement works is that an item in footage can have multiple keywords assigned to it (by setting the cID column to equal ID). My problem comes with selecting items out of footage based on that it has, say keyword WHALE *AND* keyword OTHER. I can get OR searches working easily like this, but I really would like AND. My present query goes something like this:

SELECT DISTINCT(ID) FROM footage,footage_keywords WHERE (footage.ID = footage_keywords.cID AND (footage_keywords.keyword LIKE 'WHALE%' OR footage_keywords.keyword LIKE 'OTHER%')) OR (footage_keywords.cID = footage.ID AND footage_keywords.keyword LIKE 'BOB%');

This is built from a script when the use enters this: whale and other or bob

Any advice on how to do this in one query? I can do it in a furball of multiple queries, but that would slow things down. I know my above query is flawed, I can see the logic behind that, but I can't figure out how to do an AND (using keyword= AND keyword= would imply that one keyword equals both, which it doesn't do.)

Yann

--

-------------------------------------------------------------------- Yann Ramin atr...@atrustrivalie.eu.org Atrus Trivalie Productions www.atrustrivalie.eu.org irm.it.montereyhigh.com Monterey High IT www.montereyhigh.com ICQ 46805627 AIM oddatrus Marina, CA

"All cats die. Socrates is dead. Therefore Socrates is a cat." - The Logician

# fortune "To be responsive at this time, though I will simply say, and therefore this is a repeat of what I said previously, that which I am unable to offer in response is based on information available to make no such statement."

--------------------------------------------------------------------