CODEWITHSUNDEEP
sqlsql-serverrecursionrecursive-query
sikender
sikender

Reputation: 5921

Make a recursive function in SQL Server 2005

cat_id  prod_name parent_cat_id
------ ---------- ------------
1   prod_1     2
2   prod_2     5
3   prod_3     1
4   prod_4     3
5   prod_5     7
6   prod_6     5

In a recursive function, make a table and by using these, if cat_id = 1 and parent_cat_id = 1 take that product name and if that product category id and parent category id is same then take that record also..

ANS IS LIKE :::

1   prod_1     2
2   prod_2     5
5   prod_5     7

Upvotes: 2

Views: 1063

Answers (1)

Quassnoi
Quassnoi

Reputation: 425341

WITH    rows AS
        (
        SELECT  cat_id, prod_name, parent_cat_id
        FROM    mytable
        WHERE   cat_id = 1
        UNION ALL
        SELECT  m.cat_id, m.prod_name, m.parent_cat_id
        FROM    mytable m
        JOIN    rows r
        ON      r.parent_cat_id = m.cat_id
        )
SELECT  *
FROM    rows

Upvotes: 2

Related Questions