MySQL Database Query - help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mingmeister
Forum Newbie
Posts: 1
Joined: Wed Jun 25, 2003 9:14 am

MySQL Database Query - help

Post by mingmeister »

Hi

I have a table (keyword_images) with fields
IMAGE number(6)
KEYWORD number(6)

and I want to run a query where I return a list of images that have a keyword match for all of (1,2,3,4).

IN is no good

Code: Select all

SELECT distinct image 
FROM keyword_images
WHERE keyword in (1,2,3,4)
because that give me any image that has one or more of the keywords.

AND is no good

Code: Select all

SELECT distinct image
FROM keyword_images
WHERE keyword=1 AND keyword=2 AND keyword=3 AND keyword=4
because individual records don't have all 4 keywords attached.

Anyone got any ideas?

--- Phil ---

8O
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I think you're looking at having to do a four-way self join (yuck) something like

Code: Select all

SELECT distinct k1.image 
FROM keyword_images as k1, keyword_images as k2, keyword_images as k3, keyword_images as k4
WHERE k1.image=k2.image AND k1.image=k3.image AND k1.image=k4.image AND
              k1.keyword=1 AND k2.keyword=2 AND k3.keyword=3 AND k4.keyword=4;
(I'm not a MySQL'er but I think that should work. If MySQL supports explicit joins I would do

Code: Select all

SELECT distinct image 
FROM keyword_images AS k1 
    JOIN keyword_images AS k2 USING (image)
    JOIN keyword_images AS k3 USING (image)
    JOIN keyword_images AS k4 USING (image)
WHERE k1.keyword=1 AND k2.keyword=2 AND k3.keyword=3 AND k4.keyword=4;
Post Reply