Select Value and use results from that query as Select Item in next query

Question

I'm not sure how to phrase this. I feel like I have a pretty good understanding of SQL but I'm stumped.

Bit of background.

I have a table CustomersGroups on that table exists GroupName, GroupId, ParentGroupId, ObjectRowState

Customers have the ability to create a Tree Group structure.

parent
-Child
- - GrandChild
- - GreatGrandChild
etc.

If a user deletes a group it deletes all the children associations with it. I want to be able to find a group. Then Use that Group in a Query as the Parent Group ID for its Children so on so forth..

So

SELECT * 
FROM   tbl_CustomersGroups 
WHERE  CompanyId = 123 
       And GroupName = North

Results to GroupID = 111

SELECT *
FROM   Tbl_CustomersGroups 
WHERE  ParentGroupID = 111

results to the Children GroupID 222,333,444

I then want to keep this query going over and over until there is no more groups left. Usually 6 levels deep.

Instead of separate queries over and over, I want one big query where I can put in the ParentGroupID and it will loop down and give me all the children.

I am not asking for a query to be written, I am asking for Guidance or examples as to how to go about this.

Check out this question on querying a table with a hierarchy structure: http://stackoverflow.com/questions/30536414/recursion-on-a-many-to-many-table-parent-to-child-to-parent/30537274#30537274 — Brian Pressler, Oct 23 '15 at 21:07
A self join that keeps on traversing can be achieved with a Recursive CTE. Read up more here.. https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Raj More, Oct 23 '15 at 21:16
Can I suggest at least one of you three writes these comments up as an actual Answer? Comments aren't intended for answers. — Stuart J Cuthbertson, Nov 05 '15 at 19:45

score 0 · Answer 1 · answered May 11 '16 at 10:58

I hope this can help you. You need to use CTE (Common Table Expression) https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

CREATE TABLE #table (GroupName varchar(50), 
                     GroupId int, 
                     ParentGroupId int, 
                     ObjectRowState varchar(50))

insert into #table values ('vehicle', 1, null, 'general vehicle')
insert into #table values ('Car', 2, 1, 'general cars')
insert into #table values ('2 doors car', 3, 2, 'cars with 2 doors')
insert into #table values ('4 doors car', 4, 2, 'cars with 4 doors')
insert into #table values ('Motorcycle', 5, 1, 'general Motorcycle')
insert into #table values ('Speed Motorcycle', 6, 5, 'Speed Motorcycle')
insert into #table values ('Road Motorcycle', 7, 5, 'Road Motorcycle')
insert into #table values ('Truck', 8, 1, 'General Truck')
insert into #table values ('Waggon', 9, 8, 'General Truck')
insert into #table values ('Airplane', 10, null, 'General Airplane')
insert into #table values ('Jet Airplane', 11, 10, 'General Jet Airplane')
insert into #table values ('Jet Fighter', 12, 11, 'General Jet Airplane')

;with cte (GroupName , GroupId , ParentGroupId , ObjectRowState, Grouplevel, RowOrder)
AS
(
    SELECT GroupName, 
           GroupId, 
           ParentGroupId, 
           ObjectRowState, 
           1, 
           ROW_NUMBER() OVER (ORDER BY GroupId)
    FROM #table
    WHERE ParentGroupId IS NULL
    UNION ALL
    SELECT #table.GroupName, 
           #table.GroupId, 
           #table.ParentGroupId, 
           #table.ObjectRowState, 
           cte.Grouplevel + 1, 
           cte.RowOrder
    FROM #table
    INNER JOIN cte on cte.GroupId = #table.ParentGroupId

)
SELECT GroupName, 
       GroupId, 
       ParentGroupId, 
       ObjectRowState 
FROM cte 
ORDER BY cte.RowOrder, cte.Grouplevel

DROP TABLE #table

Select Value and use results from that query as Select Item in next query

1 Answers1