Implementing a recursive query in SQL

Question

I've a question about the use of recursive SQL in which I have following table structure

Products can be in multiple groups (for the sake of clarity, I am not using int )

CREATE TABLE ProductGroups(ProductName nvarchar(50), GroupName nvarchar(50))

INSERT INTO ProductGroups(ProductName, GroupName) values 
('Product 1', 'Group 1'),
('Product 1', 'Group 2'),
('Product 2', 'Group 1'),
('Product 2', 'Group 6'),
('Product 3', 'Group 7'),
('Product 3', 'Group 8'),
('Product 4', 'Group 6')


+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |
| Product 3 | Group 7 |
| Product 3 | Group 8 |
| Product 4 | Group 6 | 
+-----------+---------+

Now the Question is I want to find out all the related products so i.e. if I pass Product 1 then I need the following result

+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |    
| Product 4 | Group 6 | 
+-----------+---------+

So basically I want to first find out all the Groups for product 1 and then for each group I want to find out all the products and so on...

1. Product 1 => Group 1, Group 2;
2. Group 1 => Product 1, Product 2 (Group 1 and Product 1 already exist so should be avoided otherwise would go into infinite loop);
3. Group 2 => Product 1 (already exist so same as above);
4. Product 2 => Group 1, Group 6 (Group 1 and Product 2 already exist)
5. Group 6 => Product 4

Answer 1

It can be done with a recursive query, but it's not optimal because SQL Server does not allow you to reference the recursive table as a set. So you end up having to keep a path string to avoid infinite loops. If you use ints you can replace the path string with a hierarchyid.

with r as (
    select ProductName Root, ProductName, GroupName, convert(varchar(max), '/') Path from ProductGroups
    union all
    select r.Root, pg.ProductName, pg.GroupName, convert(varchar(max), r.Path + r.ProductName + ':' + r.GroupName + '/')
    from r join ProductGroups pg on pg.GroupName=r.GroupName or pg.ProductName=r.ProductName
    where r.Path not like '%' + pg.ProductName + ':' + pg.GroupName + '%'
)

select distinct ProductName, GroupName from r where Root='Product 1'

http://sqlfiddle.com/#!3/a65d1/5/0

Answer 2

I don't think this is possible with a recursive CTE, because you're only allowed one recursive reference per recursive definition.

I did manage to implement it with a while loop, which is likely to be less efficient than the cte:

declare @related table (ProductName nvarchar(50), GroupName nvarchar(50))

-- base case
insert @related select * from ProductGroups where ProductName='Product 1'

-- recursive step
while 1=1
begin

    -- select * from @related -- uncomment to see progress

    insert @related select p.*
    from @related r
    join ProductGroups p on p.GroupName=r.GroupName or p.ProductName=r.ProductName
    left join @related rr on rr.ProductName=p.ProductName and rr.GroupName=p.GroupName
    where rr.ProductName is null        

    if @@ROWCOUNT = 0
        break;

end

select * from @related

You should definitely be careful with the above - benchmark on real sized data before deploying!

Answer 3

This is not going to be easy. You are modelling equivalence classes. SQL is a set langauge, and you are looking at a class - a set of sets:

https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-membership-equivalence-classes--cliques/

Answer 4

You can do this.

DECLARE @ProductGroups AS TABLE (
         ProductName NVARCHAR(50) ,
         GroupName NVARCHAR(50)
        )

INSERT  INTO @ProductGroups
        ( ProductName, GroupName )
VALUES  ( 'Product 1', 'Group 1' ),
        ( 'Product 1', 'Group 2' ),
        ( 'Product 2', 'Group 1' ),
        ( 'Product 2', 'Group 6' ),
        ( 'Product 3', 'Group 7' ),
        ( 'Product 3', 'Group 8' ),
        ( 'Product 4', 'Group 6' );
;
WITH    cte
          AS ( SELECT   a.ProductName
               FROM     @ProductGroups a
               WHERE    a.GroupName IN ( SELECT x.GroupName
                                         FROM   @ProductGroups x
                                         WHERE  x.ProductName = 'Product 1' )
             ),
        cte2
          AS ( SELECT   GroupName
               FROM     @ProductGroups
               WHERE    ProductName IN ( SELECT x.ProductName
                                         FROM   cte x )
             )
     SELECT *
     FROM   @ProductGroups
     WHERE  GroupName IN ( SELECT   x.GroupName
                           FROM     cte2 x )