Querying multiple keywords through a junction table

[Bluze]

Hi,

I'm trying to do what is usually a simple task:

I've three tables - images,keywords, and keylink.

[Images] hold the details of a photograph in a stock library.
[Keywords] holds a list of keywords that are available to be related to the images in [images]
[keylink] consists of [imageid] and [keywordid] and is used as a junction table to related multiple keywords with an image.

In access, I've created this SQL string:

SELECT images.imageID, images.fileName, keywords.keyword, keywords.keyword
FROM keywords INNER JOIN (images INNER JOIN keyLink ON images.imageID = keyLink.imageID) ON keywords.keywordID = keyLink.keywordID
WHERE (((keywords.keyword)="post" And (keywords.keyword)="box&quot);

Which is designed to display [imageid] and [filename] from the table [images] where the image is linked to both the words "post" and "box".

This string works fine when I use just one search word, but returns empty when I try more than one, as above.

Anyone any idea where I'm going wrong?

Many thanks

 

[r937]

i believe you want OR instead of AND



rudy

http://rudy.ca/

 

[Bluze]

Thanks Rudy,

But I want the imageid to be returned where the junction table links the image to both the word "post" AND the word "box" in the keywords table.

So that I can build the database like this:

Images
imageid: 1 filename: tree.jpg
imageid: 2 filename: landscape.jpg

keylink
imageID: 1 keywordID:1
imageID: 1 keywordID:2
imageID: 2 keywordID:1
imageID: 2 keywordID:2
imageID: 2 keywordID:3

keywords:
keywordID: 1 keyword: 'tree'
keywordID: 2 keyword: 'green'
keywordID: 3 keyword: 'river'

And do a search on 'tree' AND 'river'. In this case, imageID 2 would be returned.

Any ideas?

Bluze

 

[r937]

okay, i was hoping it was the simple solution, but i see you really did want AND...

the sql is a bit more complex, and i'm afraid you won't be able to get both keywords into the SELECT list easily

SELECT images.imageID
, images.fileName
FROM keywords
INNER JOIN (images
INNER JOIN keyLink
ON images.imageID = keyLink.imageID)
ON keywords.keywordID = keyLink.keywordID
WHERE keywords.keyword="post"
OR keywords.keyword="box"
group by images.imageID
, images.fileName
having count(*) = 2

rudy

 

[Bluze]

Yes, that's it!

Thanks loads.

Just one question so that I can get the SQL statement built correctly automatically - that having count(*)=2 at the end - does that refer to that number of keywords it's searching on?

Thanks again,

Bluze

 

[r937]

yes

let's say the query was looking for images that had at least three out of four keywords, you'd use count(*)>=3

rudy

 

[Bluze]

Excellent, thank you, that's the perfect solution.

All the best,

Bluze