Search code examples
sqlbitwise-operatorsbitwise-and

Bitwise in SQL Server

I need some help please, my view return the parent and child ID.

When the parentID is type N, M or SU, so the the view return the ID him self

Now, my issue is, when the child is C type, I would like to return ChildID = ParentID

Example :

  1. the type of tabley.ID 8838 is C
  2. the type of tabley.ID 8834 is M (his parentID)
  3. the type of tabley.ID 8822 is SU

The return should be :

 ChildID   ParentID
 ------------------
   8838      8834 
   8822      8822

i think my issue is in this part of code

SELECT type 
FROM tabletype 
WHERE val & 17 <> val)

cause my return now is :

 ChildID   ParentID
 ------------------
   8838      8838 
   8822      8822

Thanks a lot.

CODE:

CREATE OR ALTER  VIEW V1 (ChildID, ParentID) AS
With MyCTE (ChildID, ParentID) 
AS (    
--Anchor. Get the roots     
SELECT C_ID, C_ID as ParentID FROM tablex     
WHERE val1 is null
OR C_ID IN (SELECT C_ID FROM tabley WHERE TYPE IN (SELECT NAME FROM tabletype WHERE val& 17 <> 0)
UNION ALL     
--Recursive. Get the direct descendants of the "previous" case    
SELECT t.C_ID, M.ParentID     
FROM tablex t INNER JOIN MyCTE M on t.ID=v.val1 

WHERE t.C_ID NOT IN (SELECT ID FROM tabley) 
    OR ID IN (SELECT ID FROM tabley 
    WHERE TYPE IN (SELECT type FROM tabletype WHERE val & 17 <> val)) 
) SELECT ChildID, ParentID FROM MyCTE

tabley:

 ID   type
8822  SU
8838  C
8834  M

tabletype :

TYPE val
 C    11
 D    4
 F    11
 M    27 
 N    1 
 S    11
 SU   16
Solution

  • the Good answer is to edit :

    val& 17 <> 0 to SETUP & 11 = 0
val& 17 = 0 to SETUP & 11  <> 0

    --

    CREATE OR ALTER  VIEW V1 (ChildID, ParentID) AS
With MyCTE (ChildID, ParentID) 
AS (    
--Anchor. Get the roots     
SELECT C_ID, C_ID as ParentID FROM tablex     
WHERE val1 is null
OR C_ID IN (SELECT C_ID FROM tabley WHERE TYPE IN (SELECT NAME FROM tabletype WHERE val& 11 = 0)
UNION ALL     
--Recursive. Get the direct descendants of the "previous" case    
SELECT t.C_ID, M.ParentID     
FROM tablex t INNER JOIN MyCTE M on t.ID=v.val1 

WHERE t.C_ID NOT IN (SELECT ID FROM tabley) 
OR ID IN (SELECT ID FROM tabley 
WHERE TYPE IN (SELECT type FROM tabletype WHERE val & 11 <> val)) 
) SELECT ChildID, ParentID FROM MyCTE