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

Phil Turmel pturmel-webdev at turmel.org
Thu Feb 4 21:41:46 CST 2010


On 02/04/2010 05:57 PM, r937 wrote:
>> you can efficiently support the parent-child model:
> 
> phil, that's awesome
> 
> could you show examples applied to the original problem?
> 
> EXERCISES
>    exerciseID
>    exerciseTitle
> SEARCHTAGS
>    searchtagID
>    parentID ( -> searchtagID)
>    searchtag
> EXERCISESEARCHTAGS
>    exerciseID (Foreign key -> EXERCISES)
>    searchtagID (Foreign key -> SEARCHTAGS)
> 
> if you cannot do it for all three databases you cited, please show at
> least one solution
> 

Hi Rudy, Trav,

Yeah, I should have gone back to the original question...  The Oracle example would be simplest, as the recursion doesn't require any nested clause.

Say the SINGLE searchtag has ID "SingleID", and the branch to search has "BranchID" as the searchtagID at the apex, something like this would give you all the tags under the apex (inclusive) that have exercises that include the single searchtag:

SELECT st.searchtagID, st.searchtag, LEVEL, es1.exerciseID
FROM SEARCHTAGS AS st
  INNER JOIN EXERCISESEARCHTAGS AS es1
  ON st.searchtagID = es1.searchtagID
  INNER JOIN EXERCISESEARCHTAGS AS es2
  ON es1.exerciseID = es2.exerciseID
WHERE es2.searchtagID = :SingleID
START WITH st.searchtagID = :BranchID
CONNECT BY PRIOR st.searchtagID = st.parentID

The 'LEVEL' keyword is an Oracle predefined column that indicates how deep in the hierarchy a given row comes from.
I don't have an Oracle instance handy at the moment, so I'll leave that for y'all to test.

The 'Common Table Expression' style that Microsoft and PostgreSQL have adopted requires a bit more work.  After setting up a sample set, the following returns the entire hierarchy (tested w/ PostgreSQL v8.4):

With Recursive tags(searchtagid, searchtag, taglevel) As (
    Select st.searchtagid, st.searchtag, 1 As taglevel
    From searchtags st
    Where st.parentid is null
  Union All
    Select child.searchtagid, child.searchtag, ancestor.taglevel+1
    From searchtags child Inner Join tags ancestor
      On child.parentid = ancestor.searchtagid
  )
Select tags.searchtagid, searchtag, exercises.exerciseid, exercisetitle, taglevel
>From tags Left Join exercisesearchtags
  On tags.searchtagid = exercisesearchtags.searchtagid
  Left Join exercises On exercisesearchtags.exerciseid =  exercises.exerciseid
Order By taglevel, tags.searchtagid, exercises.exerciseid

After I defined "Curls" and "Bench Press" exercises:

1;"Body Parts";;"";1
2;"Muscles";;"";1
11;"Head";;"";2
12;"Neck";;"";2
13;"Arm";;"";2
14;"Leg";;"";2
21;"Pecs";2;"Bench Press";2
22;"Biceps";1;"Curls";2
23;"Triceps";2;"Bench Press";2
131;"Shoulder";2;"Bench Press";3
132;"Elbow";1;"Curls";3
132;"Elbow";2;"Bench Press";3
141;"Hip";;"";3
142;"Knee";;"";3

Tweaking it to fit the Trav's criteria, with branch 'Muscles' and single tag 'Elbow':

With Recursive tags(searchtagid, searchtag, taglevel) As (
    Select st.searchtagid, st.searchtag, 1 As taglevel
    From searchtags st
    Where st.searchtagid = 2
  Union All
    Select child.searchtagid, child.searchtag, ancestor.taglevel+1
    From searchtags child Inner Join tags ancestor
      On child.parentid = ancestor.searchtagid
  )
Select tags.searchtagid, searchtag, exercises.exerciseid, exercisetitle, taglevel
>From tags Inner Join exercisesearchtags est1
  On tags.searchtagid = est1.searchtagid
  Inner Join exercisesearchtags est2
  On est1.exerciseid = est2.exerciseid
  Left Join exercises On est2.exerciseid =  exercises.exerciseid
Where est2.searchtagid = 132
Order By taglevel, tags.searchtagid, exercises.exerciseid

I get:

21;"Pecs";2;"Bench Press";2
22;"Biceps";1;"Curls";2
23;"Triceps";2;"Bench Press";2

With single tag 'Shoulder' instead (131):

21;"Pecs";2;"Bench Press";2
23;"Triceps";2;"Bench Press";2

HTH,

Phil

ps.  Supposedly Oracle 8i didn't allow mixing JOIN + CONNECT BY together.  I never had to work with 8i, so I can't say for sure.


More information about the thelist mailing list