Geek Answers Handbook

Implementing a recursive query in SQL

I have a question about using recursive SQL in which I have the following table structure

Products can be in several groups (for clarity, I do not use 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 . I want to know all related products so if I transfer Product 1 , 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 know all the groups for product 1 first, and then for each group I want to know all the products, etc.

  • Product 1 => Group 1, Group 2;
  • Group 1 => Product 1, Product 2 (Group 1 and Product 1 already exist, so they should be avoided, otherwise they will go into infinity loop);
  • Group 2 => Product 1 (already exists as described above);
  • Product 2 => Group 1, Group 6 (Group 1 and Product 2 already exist)
  • Group 6 => Product 4
+6
source share
4 answers

I do not think this is possible with a recursive CTE, because you are only allowed one recursive call to a recursive definition.

I managed to implement it with a while , which is likely to be less efficient than 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

Before deploying, you must be careful with the real-size data standard described above!

+3
source

This can be done using a recursive query, but this is not optimal because SQL Server does not allow you to refer to a recursive table as a set. Therefore, you need to save the path string to avoid endless loops. If you use ints, you can replace the path string with 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

+4
source

It will not be easy. You model equivalence classes . SQL is a set of 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/

+2
source

You can do it.

 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 )
+2
source

More articles:


All Articles