mysql relational question

[gonsman]

Hello all. I am stumped. I have an image database that contains keywords for each image. The keywords are in a separate table from the images. I use a cross-reference table to link keywords to images. This is how the tables look:

image
id primary key
name
preview

image_keywords
id primary key
name

image_xref_keywords
image_id foreign key
keyword_id foreign key

If I want to search for an image that contains all of the keywords a user enters, I have no idea how to do this. It is easy to search for any of the keywords, but when I want to do all of the keywords it doesn't make sense to me. Any ideas are appreciated.

Thanks,

-Sean

[ultraslacker]

You are expecting a count of rows = number of keywords when grouped by image_id and keyword_id, so you can use group by + having.

What's keywords doing in images table?

[gonsman]

You are right, keywords should not be a part of the image table. That was a typo on my part. The actual table does not have this field.

I tried using HAVING but I am not sure how it works. Here is an actual sql query that should return results, but does not:

SELECT
	image.id AS image_id,
	image.preview,
	image_xref_keywords.keyword_id AS keyword_id
FROM
	image,
	image_xref_keywords
WHERE
	image.id > 0
	AND
	image_xref_keywords.image_id = image.id
	AND
	(
		image_xref_keywords.keyword_id = 25
		OR
		image_xref_keywords.keyword_id = 376
	)

GROUP BY image_id, keyword_id
HAVING keyword_id = 2
ORDER BY image.preview

[gonsman]

I was missing a couple of things. This works:

SELECT
	image.id AS image_id,
	image.preview,
	count(image_xref_keywords.keyword_id) AS keyword_id
FROM
	image,
	image_xref_keywords
WHERE
	image.id > 0
	AND
	image_xref_keywords.image_id = image.id
	AND
	(
		image_xref_keywords.keyword_id = 25
		OR
		image_xref_keywords.keyword_id = 376
	)

GROUP BY image_id
HAVING COUNT(image_xref_keywords.keyword_id) = 2
ORDER BY image.preview

Thanks for the help

-Sean

[leatherback]

Hi,

Your statements can become a lot shorter if you try to avoid using the same colum names in different tables.

So for image.id in images, in other tables I would use image_nr

Or, even easier:

im_id (im = table images, id = identifier)
kw_imnr (kw = keyword table, imnr: image table - nr)

Just a suggestion?

J.