StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I structure a SELECT query for the following
text
Body
copied!<p>Hoping that someone here will be able to provide some mysql advice...</p> <p>I am working on a categorical searchtag system. I have tables like the following:</p> <pre><code>EXERCISES exerciseID exerciseTitle SEARCHTAGS searchtagID parentID ( -> searchtagID) searchtag EXERCISESEARCHTAGS exerciseID (Foreign key -> EXERCISES) searchtagID (Foreign key -> SEARCHTAGS) </code></pre> <p>Searchtags can be arranged in an arbitrarily deep tree. So for example I might have a tree of searchtags that looks like this...</p> <pre><code>Body Parts Head Neck Arm Shoulder Elbow Leg Hip Knee Muscles Pecs Biceps Triceps </code></pre> <p>Now...</p> <p>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. </p> <p>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".</p> <p>Two questions:</p> <p>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...)</p> <p>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?</p> <p>Thanks in advance for your help, it's much appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload