Hello.
I am writing an application for educational research that allows users to
transcribe digital video and apply analytic keywords to segments of that
video. Users are then able to search for instances of these keywords during
the process of analyzing their data. I am in the process of altering the
program to work with MySQL, and am 98% of the way done.
Unfortunately, the implementation of Keyword Searching uses a subquery to
accomplish the AND search. I am seeking suggestions for ways to rewrite the
query so that it will work under MySQL.
Here's more detail, for those who wish to read further:
I have two data types, Episodes (whole video file records) and Clips
(smaller portions of Episodes) and a table that holds Keywords that includes
the keyword and the Episode or Clip number the keyword has been applied to.
This allows multiple keywords to be applied at either the Episode or Clip
level.
I run into trouble when I want to do a search that finds all Episodes and
Clips that have keyword combinations, (X AND Y). When going against a
different database that is largely inadequate but does support subqueries,
my query looks like this:
SELECT EpisodeNum, ClipNum FROM ClipKeywords2 CK
WHERE
(CK.KeywordGroup = 'KeywordGroup1' AND
CK.Keyword = 'X') AND
(EXISTS
(SELECT ClipNum
FROM ClipKeywords2 CK1
WHERE ((CK1.KeywordGroup = 'KeywordGroup1') AND
(CK1.Keyword = 'Y')) AND
(CK1.ClipNum = CK.ClipNum)) OR
EXISTS
(SELECT EpisodeNum
FROM ClipKeywords2 CK2
WHERE ((CK2.KeywordGroup = 'KeywordGroup1') AND
(CK2.Keyword = 'Y')) AND
(CK2.EpisodeNum = CK.EpisodeNum)))
GROUP BY ClipNum, EpisodeNum
That works. Any suggestions for what I can replace it with that will work
under MySQL? Can a Temporary Table be created from elements in either of
two other tables? Any other ideas or suggestions?
If my only option is to wait for MySQL 4.1, any idea when that will be
available?
Thanks,
David K. Woods, Ph.D.
Wisconsin Center for Education Research
University of Wisconsin, Madison
http://www.Transana.org
"In theory, there is no difference between theory and practice. But, in
practice, there is. " - Jan L.A. van de Snepscheut
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
