one to many question?

[megalith]

I have 2 tables - images and keywords

The images table contains an imageID and an imageFileName and the keywords
table contains a keywordID, imageID (FK), and keyword

the keyword table can have many keywords associated with an imageID and I have
no problem searching for imageID's that contain a certain keyword. My problem
arises when I want to retrieve imageID's that contain one keyword but not
another. I'm not sure how to write it in one query.

as an example if I wanted to retrieve all imageID's (UNIQUE) from the keywords
table that had the keyword "automobile" but NOT the keyword "truck".

Thanks in advance for any help

[Dan Bracuk]

syntax is database specific. this should show you the logic you need

select distinct imagefilename
from images, join keywords using (imageid)
where keyword like '%automobile%'
and imageid in
(select imageid from images
and not exists
(select imageid from images join keywords using (imageid)
where keyword like '%truck%'));

By the way, you might be better off with 3 tables, images, keywords, and
images_keywords which would have a many to many relationship to the other two
tables. Kinda hard to tell.


Originally posted by: megalith

I have 2 tables - images and keywords

The images table contains an imageID and an imageFileName and the keywords
table contains a keywordID, imageID (FK), and keyword

the keyword table can have many keywords associated with an imageID and I have
no problem searching for imageID's that contain a certain keyword. My problem
arises when I want to retrieve imageID's that contain one keyword but not
another. I'm not sure how to write it in one query.

as an example if I wanted to retrieve all imageID's (UNIQUE) from the keywords
table that had the keyword "automobile" but NOT the keyword "truck".

Thanks in advance for any help

[megalith]

Thanks for the reply Dan.

It was suggested to me before that a third table might be the way to go but I
don't understand sql enough to know how I would set that up. You say it's kind
of hard to tell if I need that third table but what kind of criteria would I
use to make a decision on that? I'm starting from scratch on this DB and want
it to be set up properly as it may grow to hundreds of thousands of rows. I
obviously want to find out sooner than later what is the best design.

thanks again for the response

[mxstu]

megalith,

You should typically use three tables when you have a many-to-many
relationship, where one record in the first table can relate more than one
record in a second table (and vice versa). For example let's say you have an
application dealing with college courses:

Students
-----------
StudentID
FirstName
LastName

Courses
------------
CourseID
CourseTitle

A student can sign up for more than one course, so one record in the "Student"
table can be associated with more than one course. A course can be attended by
more than one student, so a single "Course" record can be associated with more
than one "Student" record. In this situation you would typically create a
third table to store the associated records:

Courses
------------
StudentID
CourseID

You would then do a JOIN between the three tables to find out what courses a
student is taking, or what students are attending a particular course.

I would assume that one "keyword" can be associated with more than one "image"
and vice versa, so you probably should have a third table called
"ImageKeyword" that would contain just the imageID and associated keywordID.

[megalith]

I think I'm understanding this better now. Let me explain a bit more and maybe
someone can tell me if I'm on the right track.

this database will hold image info for many images and clients. As an example
I have one client who has 7000 images and 87,000 keywords of which 10,000 are
unique. So the 7000 images go in the images table, the 10,000 unique keywords
(or key phrases) go in the keywords table and the joining table will have
87,000 rows relating the images to the keywords. Sound correct?

Now when a user wants to upload a new image with say 20 keywords (this is in a
comma delimited list) I will then run a query on each keyword to check if it
already exists in the keyword table and if not then add it. (I just want to
make sure running a loop with 20 queries is the way to handle this)

This data base will need to support possibly hundreds of users (most will
probably have 500 or less images). Does this setup sound like the best way to
go?

any advice would be very appreciated.

thanks

[Dan Bracuk]

You don't have to run 20 queries, you can do something like this:

select keyword
from keywords
where 1=1 -- alway true
and
( 1= 1 -- need this for correct syntax
<cfloop list= newkeywords index = "thiskeyword">
or keyword = '#thiskeyword#'
</cfloop>
)

You then compare the query results to your original list to generate a,
hopefully shorter, list of new keywords. You then do your inserts by looping
through the list of new keywords.

Originally posted by: megalith
I think I'm understanding this better now. Let me explain a bit more and maybe
someone can tell me if I'm on the right track.

this database will hold image info for many images and clients. As an example
I have one client who has 7000 images and 87,000 keywords of which 10,000 are
unique. So the 7000 images go in the images table, the 10,000 unique keywords
(or key phrases) go in the keywords table and the joining table will have
87,000 rows relating the images to the keywords. Sound correct?

Now when a user wants to upload a new image with say 20 keywords (this is in a
comma delimited list) I will then run a query on each keyword to check if it
already exists in the keyword table and if not then add it. (I just want to
make sure running a loop with 20 queries is the way to handle this)

This data base will need to support possibly hundreds of users (most will
probably have 500 or less images). Does this setup sound like the best way to
go?

any advice would be very appreciated.

thanks

[philh]

I hope your DB can handle bulk inserts.
This creates a statement that will bulk insert the image key/keyword key into
your cross-reference table. Yes, you should have one of those.
<cfset myinsertlist="">
<cfloop index="listitem" list="#mylistofvalues#">
<cfset myinsertlist=myinsertlist&imagekey&","&listitem&" UNION SELECT ">
</cfloop>
<Cfset myinsertlist = mid(myinsertlist,1,len(myinsertlist)-13)>
<cfoutput>
INSERT INTO Mycrossreftable
SELECT
#myinsertlist#
</cfoutput>

Any DB worth its salt can handle the many-to-many volume that you describe.

HTH,

[megalith]

Thanks Dan and Phil for your examples!

I thnk the clouds are starting to part and I'm seeing the light. I was
wondering if one fo you guys could show me just how the insert statement would
look using the image and three keywords in the example below. I need a visual
to get it through my thick head. I assume that it's one insert statement that
inserts into all three tables at once? or is it two or more with a UNION?

<cfset image = "myImage.jpg">
<cfset keywords ="sky, water, mountain">

ImageTable
------------------
imageID (pk)
ImageName


crossReferenceTable
------------------
crtID (pk)
ImageID (fk)
KeywordID (fk)


keywordTable
----------------------
KeywordID (pk)
Keyword


I think an an example using my table names and column names will set me
straight. Thanks in advance for your help.

[paross1]

You can only insert into one table at a time, so you probably should include all insert statements with a single CFTRANSACTION so that you can rollback if necessary, and commit as a unit.

Phil

[megalith]

Dan,

This query you suggested returns all keywords in the keywords table instead of
just the duplicates found in my keyword list. I'm not familiar with sql enough
to know how to adjust this query. Any suggestions?

thanks to both you and Phil for all your help

<cfquery datasource="mydb" name="checkForDups">
select keyword
from keywords
where 1=1
and
( 1= 1
<cfloop list= "keywordList" index = "thiskeyword">
or keyword = '#thiskeyword#'
</cfloop>
)
</cfquery>

[Dan Bracuk]

Oops. Change this:
where 1=1
and
( 1= 1

to this
where 1=1
and
( 1= 2



Originally posted by: megalith
Dan,

This query you suggested returns all keywords in the keywords table instead of
just the duplicates found in my keyword list. I'm not familiar with sql enough
to know how to adjust this query. Any suggestions?

thanks to both you and Phil for all your help

<cfquery datasource="mydb" name="checkForDups">
select keyword
from keywords
where 1=1
and
( 1= 1
<cfloop list= "keywordList" index = "thiskeyword">
or keyword = '#thiskeyword#'
</cfloop>
)
</cfquery>

[mxstu]

Don't you mean ... ?

select keyword
from keywords
where 1 = 0
<cfloop list= "keywordList" index = "thiskeyword">
or keyword = '#thiskeyword#'
</cfloop>

Although, a WHERE IN (list of values) clause might be more readable and in
both cases, the list values should be trimmed to avoid problems due to
trailing/leading spaces.

[megalith]

mxstu,

that one worked for me. thank you

I have two more questions now that I have the insert working. For reference,
here is my table structures again...

ImageTable
------------------
imageID (pk)
ImageName


Image_Keyword_Rel
------------------
crtID (pk)
ImageID (fk)
KeywordID (fk)


keywordTable
----------------------
KeywordID (pk)
Keyword

My first question applies to my code that I use to query the tables for a
keyword match. Is the code below the most efficient way to do this query?
Seems to run quickly now but I am only searching against 15,000 images, 25,000
keywords, and 225,000 entries in the joining table with my test database.

<cfquery name="GetResults" datasource="MyDB">
SELECT Images.ImageID,Images.ImageName
FROM images
INNER JOIN (Keywords INNER JOIN Image_Keyword_Rel ON Keywords.keywordID =
Image_Keyword_Rel.KeywordID)
ON Images.ImageID = Image_Keyword_Rel.ImageID
WHERE Keywords.Keyword = '#Searchstring#'
</cfquery>

My second question relates to modifying the query above to search for a
certain keyword but not another. For example, I want to retrieve a list of
images that contain "transportation" in their respective keyword list but not
"truck" in the same list. I hope that makes sense.

Thanks again for everyones help on this I really appreciate it.

[mxstu]

megalith,

That query looks fine. Appropriate indexes will also help performance. For
example, if your application will frequently search for exact "keyword"
matches, then the "keyword" column would be a good candidate for indexing. Be
aware that indexes are often ignored when using WHERE LIKE '%pattern%' and
searching with wildcards is typically slower. The "ImageID" + "KeywordID"
columns in the "Image_Keyword_Rel" table are also good candidates for a unique
constraint / index.

> search for a certain keyword but not another...

There are a few ways to do it. I would probably use a WHERE NOT EXISTS
clause. This could be optimized, the basic idea is:



<!--- adjust syntax for your database type --->
SELECT i.ImageID,i.ImageName
FROM images i INNER JOIN image_Keyword_Rel r ON i.imageID = r.imageID
INNER JOIN keywords k ON k.keywordID = r.keywordID
WHERE k.Keyword = 'transportation' AND NOT EXISTS
( SELECT 1
FROM keywords ek INNER JOIN image_Keyword_Rel er
ON ek.keywordID = er.keywordID
WHERE ek.keyword = 'truck' AND
er.imageID = i.imageID
)

[megalith]

mxstu,

Thanks again for your help. I did get an error with the code you posted and
since I'm still a noob when it comes to this stuff I was hoping you might check
it out for me. I can't quite wrap my head around the code you posted yet but I
will...

error:
Syntax error (missing operator) in query expression 'i.imageID = r.imageID
INNER JOIN keywords k ON k.keywordID = r.keywordID'.

Thanks

[mxstu]

megalith,

Are you using Access?

[megalith]

I am for now while I develop the DB but I will be converting it to MySQL. I'm
used to access and find it much easier to create a DB with.

You asking that brings up another error I received when adding keywords. This
piece of code:
<cfloop list= "keywordList" index = "thiskeyword">
or keyword = '#thiskeyword#'
</cfloop>

that I use in the select statement looking for duplicates will error out if
there are around a 100 or more keywords that I am comparing. Could this be an
Access problem also?

I'm assuming I should convert this DB to MySQL sooner than later, even for
testing

[mxstu]

megalith,

Okay. Access requires parenthesis around the JOIN statements whereas most
other databases don't. That's what is causing the error with the code I
posted. I don't know the exact Access syntax but it should be similar to the
FROM clause in your last query. Maybe something like this?

--- not tested
.....
FROM images i INNER JOIN
(keywords k INNER JOIN image_Keyword_Rel r ON k.keywordID =
r.keywordID)
ON i.imageID = r.imageID

[megalith]

Thanks mxstu, worked great for access. The conversion to MySQL is processing
right now and I will start using a real DB when it's finished.

what do you think about that other error I mentioned in the select loop? Is
that an access limitation?

[mxstu]

megalith,

It's guess it's possible. I don't use Access much, so I cannot say for certain.

I would probably use a WHERE IN (...) clause instead of multiple OR
statements. The basic concept is the same, but the code is a bit more
readable. The attached example needs some additional validation (ie. check the
list size), but you should get the basic idea. Note - I typically use an
additional WHERE NOT EXISTS statement with the INSERT's to ensure that no
duplicate keywords are created.




<!--- must remove trailing/leading spaces from list elements to avoid errors
--->
<cfset newKeywordList = reReplace(trim(keywordList),
"([[:space:]]*,[[:space:]]*)", ",", "all")>

<!--- create list of keywords that already exist --->
<cfquery name="findExisting" datasource="#yourDSN#">
SELECT keyword
FROM keywords
WHERE keyword IN
(
<cfqueryparam value="#newKeywordList#" cfSqlType="cf_sql_varchar"
list="yes">
)
</cfquery>
<cfset existingKeywordList = valueList(findExisting.keyword)>

<!--- loop through new words and insert if they do not already exist --->
<cfloop list="#newKeywordList#" index="newKeyword">
<cfif listFindNoCase(existingKeywordList, newKeyword) eq 0>
<cfquery name="addNewKeyword" datasource="#yourDSN#">
INSERT INTO keywords (keyword)
VALUES ('#newKeyword#')
</cfquery>
</cfif>
</cfloop>