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
---------------------------------------------------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