Just wanted to throw in a quick write up about common table expressions (CTE) in sql server.
Imagine you have a hiearchy of units (like military units) though this same thing can apply to other cases like attributes or types. Working with some made-up data you may have:
I have a case where a user will be assigned to just one level of these units but being assigned to one level means that all child units also apply to the user.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[_Unit_GetByUserId] ( @UserId [uniqueidentifier] ) AS with c as ( select b.* from Unit b, UserUnit u where u.UserId = @UserId and b.Id = u.UnitId union all select b.* from Unit b join c on b.ParentId = c.Id where b.Id != b.ParentId ) select * from c
This allows me to get all the units that apply to the user. In this case, if a user is assigned to the 9th Brigade then the stored proc will return: 9th Brigade, 17th Regiment, 21st Regiment
The key is in the union where the join to the CTE is and the ParentId is being set to the Id coming from the CTE.
I realize there isn’t much explanation here as to what is happening but if you follow the links above you’ll get a better explanation then I could give.