ANDing across tables

From: abracad <abracad_at_nospam.com>
Date: Wed, 15 Aug 2001 20:57:20 GMT
Message-ID: <3b7ae22d.6169125@news.freeserve.net>

I have a database of images, each of which is described by n keywords. The data is held in 3 tables:
image: description, id
keyword: keyword, id
imkw: im_id, kw_id

I would like to run a query which retrieves images having 2 (or more keywrods)

I can achieve this thus:
SELECT image.id, COUNT(*)
FROM image, imkw, keyword
WHERE image.id=imkw.im_id AND imkw.kw_id=keyword.id AND keyword IN ("kimono", "shrine")
GROUP BY image.ID
HAVING COUNT(*)=2 But I would like to know if there is a more efficient means of running such and AND search.

Many thanks in advance for any advice. Received on Wed Aug 15 2001 - 15:57:20 CDT