[thelist] How do I SELECT for the following...

Trav rabbit_fufu at hotmail.com
Tue Feb 2 14:25:50 CST 2010


Hoping that someone here will be able to provide some mysql advice...
I am working on a categorical searchtag system. I have tables like the following:
EXERCISES
    exerciseID
    exerciseTitle

SEARCHTAGS
    searchtagID
    parentID ( -> searchtagID)
    searchtag

EXERCISESEARCHTAGS
    exerciseID (Foreign key -> EXERCISES)
    searchtagID (Foreign key -> SEARCHTAGS)

Searchtags can be arranged in an arbitrarily deep tree. So for example I might have a tree of searchtags that looks like this...

Body Parts
    Head
    Neck
    Arm
        Shoulder
        Elbow
    Leg
        Hip
        Knee
Muscles
    Pecs
    Biceps
    Triceps

Now...
I want to select all of the searchtags in ONE branch of the tree that reference at least ONE record in the subset of records referenced by a SINGLE searchtag in a DIFFERENT branch of the tree.
For example, let's say the searchtag "Arm" points to a subset of exercises. If any of the exercises in that subset are also referenced by searchtags from the "Muscles" branch of SEARCHTAGS, I would like to select for them. So my query could potentially return "Biceps," "Triceps".
Two questions:
1) What would the SELECT query for something like this look like? (If such a thing is even possible without creating a lot of slow down. I'm not sure where to start...)
2) Is there anything I should do to tweak my datastructure to ensure this query will continue to run fast - even as the tables get big?
Thanks in advance for your help, it's much appreciated. 		 	   		  
_________________________________________________________________
Check your Hotmail from your phone.
http://go.microsoft.com/?linkid=9708121


More information about the thelist mailing list