I have two tables
permisos , where hierarchy has an ID, a ID_PADRE (pointing to the same table to a parent node) and a DESCRIPTION of the node itself. The permissions table has an ID_OPERATOR (id of the user that is logged in the system), ID_NODO (id of the node for which I am specifying permissions, foranea to the table
jerarquia ) and R (which is a BIT that indicates if it has read permissions), not all nodes have a specified permission, there may be nodes that do not appear in the permissions table
I'm doing a query that brings you the nodes that match a criterion and iteratively brings the superior nodes of each branch, that algorithm worked well, but now that the permissions table was added if a node has R = 0 (that is, you have read permissions denied) the entire branch that hangs on it should not be seen. So far I have this query, but the truth is I'm pretty stuck:
SET NOCOUNT ON DECLARE @TablaFiltrada TABLE ( ID INT, ID_PADRE INT, DESCRIPCION NVARCHAR(200), R BIT ) DECLARE @Rows INT INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R) SELECT J1.ID, ID_PADRE, DESCRIPCION, P.R FROM jerarquia J1 LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = 1002) P ON J1.ID = P.ID_NODO WHERE DESCRIPCION LIKE 'keyword' AND (R IS NULL OR R = 1) SELECT @Rows = @@ROWCOUNT WHILE @Rows <> 0 BEGIN INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R) SELECT P2.ID, P2.ID_PADRE, P2.DESCRIPCION, P.R FROM jerarquia P2 INNER JOIN @TablaFiltrada P1 ON P2.ID = P1.ID_PADRE LEFT JOIN @TablaFiltrada P3 ON P3.ID = P2.ID LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = 1002) P ON P2.ID = P.ID_NODO WHERE P3.ID IS NULL SELECT @Rows = @@ROWCOUNT -- Sigo iterando END SELECT DISTINCT * FROM @TablaFiltrada WHERE R IS NULL OR R = 1 ORDER BY DESCRIPCION
Thank you very much!