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...

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

I don't think the snippet syntax is a good idea in this context, even if the output you want is formatted as HTML. I was very tempted to just remove it, but I'm not sure what significance attaches to the CSS section of the output. I think it should be omitted, but it is your question. — Jonathan Leffler
– Jonathan Leffler, Commented Jan 22, 2016 at 1:49
I'd suggest just making ascii tables using something like ozh.github.io/ascii-tables — Blorgbeard
– Blorgbeard, Commented Jan 22, 2016 at 2:01
Sorry it was by mistake just edited so it's showing correct output now... — Tejas Patel
– Tejas Patel, Commented Jan 22, 2016 at 2:21
Does it need to be a view, or are you working in the context of a stored procedure? — Greg Viers
– Greg Viers, Commented Jan 22, 2016 at 2:24
either way fine but I think so in my case I will be using stored procedure. — Tejas Patel
– Tejas Patel, Commented Jan 22, 2016 at 2:26

gordy · Accepted Answer · 2016-01-22 02:46:04Z

4

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

answered Jan 22, 2016 at 2:46

gordy

9,9713 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirk Broadhurst Over a year ago

Clever answer. The path can be of any format, and is required to stop the recursive loop.

Blorgbeard · Accepted Answer · 2016-01-22 02:32:56Z

3

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!

edited Jan 22, 2016 at 2:32

answered Jan 22, 2016 at 2:30

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

3 Comments

Greg Viers Over a year ago

This will work as long as the table is small. Performance will be a problem. O(x^2)

Blorgbeard Over a year ago

Yes indeed. I'm hoping someone will come along with a "proper" solution.

Greg Viers Over a year ago

I think this is the best OP can do. SQL is not the right language for this, IMO

Greg Viers · Accepted Answer · 2016-01-22 02:30:36Z

2

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/

answered Jan 22, 2016 at 2:30

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Comments

Robert Paulsen · Accepted Answer · 2016-01-22 04:46:22Z

2

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 )

answered Jan 22, 2016 at 4:46

Robert Paulsen

5,2113 gold badges23 silver badges28 bronze badges

2 Comments

Tejas Patel Over a year ago

Tejas Patel Over a year ago

My apologies as above comment is not that readable but comments does not allow ascii table. So if we add one more record in input table as (Product 4 and Group 7) then all 8 records should be shown in output but the solution you provided only shows 5 rows. sqlfiddle.com/#!6/c641f4/1

Collectives™ on Stack Overflow

Implementing a recursive query in SQL

4 Answers 4

1 Comment

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related