[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