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

[Trav]

Thank you so much for your replies, that's awesome.  I'm still in the early phases of the project so looking at a different database is an option.    Food for thought...
Thanks again!
trav



> Date: Thu, 4 Feb 2010 22:41:46 -0500
> From: pturmel-webdev at turmel.org
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] How do I SELECT for the following...
> 
> 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.
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt ! 
 		 	   		  
_________________________________________________________________