select statement woes

[megalith]

I hope some one can help me figure this out. I've got a search feature for
images and users can enter up to 3 terms and I'm not getting the results I need.

I have 3 tables.
1 - images - holds basic info about the images
2 - keywords - holds a list of keywords that all clients share and can add to
3 - keyword relationship table - links images with the keywords associated to
it

I also set up three images to test with and they have these keywords:

image1 - ski, snow, mountain, terrain
image2 - ski, snow, mountain'
image3 - ski

single word searches are fine, but if I search for 2-3 terms I get a blank
result.

Any help would be greatly appreciated. Thanks


<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I
INNER JOIN image_Keyword_Rel R ON I.imageID = R.imageID
AND R.ClientID = #ClientID#
INNER JOIN keywords K ON K.keywordID = R.keywordID
WHERE K.Keyword = '#term1#'
AND K.Keyword = '#term2#'
AND K.Keyword = '#term3#'
ORDER BY I.SortOrder
</cfquery>

[LeftCorner]

I don't know if this will work but it may be worth a try. Its hard to tell
without having access to your schema and data. I used a three way join the
kewyords table as a subquery. You could probably use the INTERSECT command for
a more graceful query but I'm using MySQL which doesn't support INTERSECT.

I assume you have imageID as a foreign key in your keywords table:





<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I

INNER JOIN image_Keyword_Rel R ON I.imageID = R.imageID
AND R.ClientID = #ClientID#

INNER JOIN keywords K ON K.keywordID = R.keywordID

WHERE K.imageID IN
(
SELECT k1.imageID
FROM Keywords k1, Keywords k2, Keywords k3

WHERE 1 = 1
AND k1.imageID = k2.imageID
AND k2.imageID = k3.imageID

<cfif Len(Trim(term1))>
AND k1.keyword = '#term1#'
</cfif>
<cfif Len(Trim(term2))>
AND k2.keyword = '#term2#'
</cfif>
<cfif Len(Trim(term3))>
AND k3.keyword = '#term2#'
</cfif>
)

ORDER BY I.SortOrder
</cfquery>

[Dan Bracuk]

Originally posted by: megalith

I hope some one can help me figure this out. I've got a search feature for
images and users can enter up to 3 terms and I'm not getting the results I need.

I have 3 tables.
1 - images - holds basic info about the images
2 - keywords - holds a list of keywords that all clients share and can add to
3 - keyword relationship table - links images with the keywords associated to
it

I also set up three images to test with and they have these keywords:

image1 - ski, snow, mountain, terrain
image2 - ski, snow, mountain'
image3 - ski

single word searches are fine, but if I search for 2-3 terms I get a blank
result.

Any help would be greatly appreciated. Thanks


<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I
INNER JOIN image_Keyword_Rel R ON I.imageID = R.imageID
AND R.ClientID = #ClientID#
INNER JOIN keywords K ON K.keywordID = R.keywordID
WHERE K.Keyword = '#term1#'
AND K.Keyword = '#term2#'
AND K.Keyword = '#term3#'
ORDER BY I.SortOrder
</cfquery>

Change this:
WHERE K.Keyword = '#term1#'
AND K.Keyword = '#term2#'
AND K.Keyword = '#term3#'

to this
WHERE K.Keyword = '#term1#'
or K.Keyword = '#term2#'
or K.Keyword = '#term3#'

or this
WHERE K.Keyword in( '#term1#', '#term2#', '#term3#')

[megalith]

LeftCorner,

Thanks for the sample code. I don't have the imageID in the keywords table.
I have a third table called image_Keyword_Rel that has the imageID and the
KeywordID. I didn't want the keywords table to contain duplicate keywords and
the way it's setup the images can share keywords and the images and keywords
are connected thru the relationship table.

I tried changing your code a bit to get it to work but didn't have any luck.
Maybe you can suggest another way to write it?

thanks



--------------------------------------------------------------------------------
-----------------
Dan,

Your suggestion of using "or" instead of and is how I have it running now but
it returns to many irrelevant records. If someone is looking for "ski and
Mountain and snow" I need results to be returned that have ALL three of those
terms. The "or" gives me any combination.

I also tried this "WHERE K.Keyword in( '#term1#', '#term2#', '#term3#')" as
you suggested but MySQL 4.1 didn't seem to like it.

any other suggestions would be appreciated, thanks

[megalith]

Here is how my tables are setup:

images (imageID, clientID, imagename, imagenumber, ImageVert, ImageHorz)
image_Keyword_Rel (imageFKID, ImageID, KeywordID)
Keywords (keywordID, keyword)

[paross1]

This is just a SWAG, but is it close to what you are trying to do?

Phil

<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I
INNER JOIN image_Keyword_Rel R ON I.imageID = R.imageID
AND R.ClientID = #ClientID#
WHERE
EXISTS (SELECT 1
FROM keywords K
WHERE K.keywordID = R.keywordID
AND K.Keyword = '#term1#')
<cfif IsDefined "term2>
AND EXISTS (SELECT 1
FROM keywords K
WHERE K.keywordID = R.keywordID
AND K.Keyword = '#term2#')
</cfif>
<cfif IsDefined "term3>
AND EXISTS (SELECT 1
FROM keywords K
WHERE K.keywordID = R.keywordID
AND K.Keyword = '#term3#')
</cfif>
ORDER BY I.SortOrder
</cfquery>

[megalith]

Phil,

I tried your code and it didn't return any results. I searched for
"ski,terrain,snow" for which there is one image associated to those keywords.

Your code did work with only one term submitted though, but not two or three.

Any other ideas?

Thanks for helping!

[paross1]

Hmmmm....perhaps this?

Phil


<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I
WHERE
EXISTS (SELECT 1
FROM keywords K
INNER JOIN image_Keyword_Rel R ON K.keywordID = R.keywordID
AND R.ClientID = #ClientID#
WHERE I.imageID = R.imageID
AND K.Keyword = '#term1#')
<cfif IsDefined "term2>
AND EXISTS (SELECT 1
FROM keywords K
INNER JOIN image_Keyword_Rel R ON K.keywordID = R.keywordID
AND R.ClientID = #ClientID#
WHERE I.imageID = R.imageID
AND K.Keyword = '#term2#')
</cfif>
<cfif IsDefined "term3>
AND EXISTS (SELECT 1
FROM keywords K
INNER JOIN image_Keyword_Rel R ON K.keywordID = R.keywordID
AND R.ClientID = #ClientID#
WHERE I.imageID = R.imageID
AND K.Keyword = '#term3#')
</cfif>
ORDER BY I.SortOrder
</cfquery>

--or--

<cfquery name="GetResults" datasource="#Datasource#">
SELECT DISTINCT I.ClientID, I.Imagename, I.imageNumber, I.ImageVert,
I.ImageHorz
FROM images I
WHERE
EXISTS (SELECT 1
FROM keywords K, image_Keyword_Rel R
WHERE K.keywordID = R.keywordID
AND I.imageID = R.imageID
AND R.ClientID = #ClientID#
AND K.Keyword = '#term1#')
<cfif IsDefined "term2>
AND EXISTS (SELECT 1
FROM keywords K, image_Keyword_Rel R
WHERE K.keywordID = R.keywordID
AND I.imageID = R.imageID
AND R.ClientID = #ClientID#
AND K.Keyword = '#term2#')
</cfif>
<cfif IsDefined "term3>
AND EXISTS (SELECT 1
FROM keywords K, image_Keyword_Rel R
WHERE K.keywordID = R.keywordID
AND I.imageID = R.imageID
AND R.ClientID = #ClientID#
AND K.Keyword = '#term3#')
</cfif>
ORDER BY I.SortOrder
</cfquery>

[megalith]

Phil,

The last one did it!
Thank you so much for your time on this I really appreciate it.

Bart