CODEWITHSUNDEEP
sqlsql-servert-sql
arjun
arjun

Reputation: 645

Recursive table select query

I have the following two table. Basically the first table is recursive i.e it store both parent and child data. The second table has a relationship with first one between menuid and pageid. The second table store information of any child row of first table ( any row having value in link field).Can anyone help me write a query that should remove any child row which has no data in second table including their parent( the portion mark with red rectangles(first table). In short only three rows get retrieve i.e menuid=1,3,4

enter image descri![enter image description here

enter image description here

Upvotes: 0

Views: 41

Answers (1)

MarkD
MarkD

Reputation: 5316

This should work;

;WITH MyCTE AS
(
    SELECT   T1.MenuId
            ,T1.MenuName
            ,T1.ParentId
            ,T1.Link
            ,T1.Active
    FROM Table1 T1
    WHERE EXISTS (SELECT * FROM Table2 T2 WHERE T1.MenuId = T2.PageID)
    UNION ALL
    SELECT   T1.MenuId
            ,T1.MenuName
            ,T1.ParentId
            ,T1.Link
            ,T1.Active
    FROM Table1 T1
    JOIN MyCTE  MC  ON MC.ParentId = T1.MenuId
)
SELECT DISTINCT *
FROM MyCTE;

It would have been a lot easier to test if you had provided the DDL and DML :)

Upvotes: 1

Related Questions