Microsoft wrote:
I've a table of users like this
id_user id_ref flg_enabled
1 0 1
2 1 1
3 1 0
4 2 1
id_user 1 created id_user 2 and 3
id_user 2 created id_user 4
This rule can by infinite
I need a recursive function that tells me when a user and all his
"fathers" are enabled
If one of its father is disabled (flg_enabled=0) the function have to
return 0
If all the father (till id_ref=0) are enabled (flg_enabled=1) the
function have to return 1
function is_enabled(id_user)
....
End Function
If you are using MS SQL Server, you can use a user-defined function:
CREATE FUNCTION dbo.is_enabled(
@ID INT
) RETURNS BIT AS
BEGIN
DECLARE @Product INT, @Parent INT
SELECT @Product = flg_enabled,
@Parent = id_ref
FROM YourTable
WHERE id_user = @ID
IF @Parent > 0 SET @Product = @Product * dbo.is_enabled(@Parent)
RETURN @Product
END
Test your results:
SELECT *, dbo.is_enabled(id_user) AS is_enabled FROM YourTable
Results:
id_user id_ref flg_enabled is_enabled
1 0 1 1
2 1 1 1
3 1 0 0
4 2 1 1
5 3 1 0
6 2 0 0
7 4 1 1
Be aware that SQL Server "only" allows 32 levels of recursion, so if an
element has 32 "fathers", this will fail.
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.