1:n Join Question

[kc9ddi]

I'm developing a simple photo album project.  I have one table that stores information about each photo (filename, format, size, etc.) and then a table of keywords.  The keywords table looks like:

[code]
id INT primary key,
keyword VARCHAR,
photo_id INT
[/code]

Each keyword gets its own row in the table, even if multiple photos have the same keyword.  Also, each photo can have multiple keywords that point to the same photo_id.  I think this is a pretty standard setup.

What I'm trying to do is something like SELECT * FROM photos WHERE keyword='keyword1' AND keyword='keyword2' -- that is, find all photos that have BOTH keyword1 and keyword2.  I figured out a JOIN statement that does this for keyword1 OR keyword2, but am having trouble working one that does AND.

Any ideas?

[artacus]

Join the keywords table twice, once for each keyword. Just give the tables different aliases.

[kc9ddi]

I see - so, then, I'd need an additional join for each keyword I want to search?  eg, 4 keywords = 4 joins?

[kc9ddi]

I hate to ask this, but I'm having trouble working out the syntax for the SQL - would you mind giving me an example?

[artacus]

[code]
SELECT p.*
FROM photos AS p
JOIN keywords AS k1 ON p.photo_id = k1.photo_id
JOIN keywords AS k2 ON p.photo_id = k2.photo_id
WHERE k1.keyword LIKE 'keyword1' AND k2.keyword LIKE 'keyword2'
[/code]

or if you wanted to  do it weighted where it could match either one or both of the keywords

[code]
SELECT p.*,
CASE WHEN k1.id IS NOT NULL AND k2.id IS NOT NULL THEN 2
    WHEN k1.id OR k2.id IS NOT NULL THEN 1
    ELSE 0 END AS search_score
FROM photos AS p
LEFT JOIN keywords AS k1 ON p.photo_id = k1.photo_id AND  k1.keyword LIKE 'keyword1'
LEFT JOIN keywords AS k2 ON p.photo_id = k2.photo_id AND k2.keyword LIKE 'keyword2'
GROUP BY p.photo_id HAVING search_score > 0 ORDER BY search_score DESC
[/code]

[fenway]

Fancy... however, depending on how expensive this is, you many simply want to get back all records with any keyword, and do the scoring yourself.