A Parent-Child Logic (SQL needed)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

A Parent-Child Logic (SQL needed)

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Sounds like you want a recursive select query.
waqas_punjabian
Forum Commoner
Posts: 67
Joined: Wed Aug 10, 2005 9:53 am

Post by waqas_punjabian »

Yes I think so, But can anyone tell me how to write the Query for above problem ?
Post Reply