Page 1 of 1

A Parent-Child Logic (SQL needed)

Posted: Fri Apr 13, 2007 1:31 pm
by waqas_punjabian
Can you please tell me the SQL for following scenario. (remember, I only need SQL Select Query, don't want any PHP algo for that)

Code: Select all

tbl_Category
--------------------------
CategoryID        Parent
--------------------------
1                       0
2                       1
3                       0

4                       1
5                       3
6                       2 

7                       2
8                       5
9                       5

---------------------------

Code: Select all

tbl_Multiplier
----------------------------------------------------
MultiplierID     Multiplier          CategoryID
----------------------------------------------------
1                      0.5                    1
2                      0.2                    2
3                      1                      5

4                      0.7                    7
5                      0.6                    8
---------------------------------------------------
e.g) Select tbl_Multiplier.Multiplier .......... Where tbl_Category.CategoryID = 6

I need only the multiplier of the current selected Category in following condition.
1) Select Multiplier for category ID 2. That's Fine It exists in tbl_Multiplier.
2) Select Multiplier for category ID 6. Now here's the basic problem, It does not exist in tbl_Multiplier. The requirement is => It should fetch the parent's Multiplier if it does not exist against the desired categoryID, and if it even does not exist against the parent then it should go for grand parent and continuous Until parent=0.

In point (2) It should select 0.2, because CategoryID 2 is the parent of 6. Now if it will also not be there then it should search for 2's parent that is 1.

I am just gona mad in this problem, I've thought several times but couldn't make a Query for this problem, or please tell me is it even possible in SQL or else I must have to write PHP for the solution ?

regards,

Waqas

Posted: Fri Apr 13, 2007 4:44 pm
by RobertGonzalez
Sounds like you want a recursive select query.

Posted: Mon Apr 16, 2007 2:13 am
by waqas_punjabian
Yes I think so, But can anyone tell me how to write the Query for above problem ?