[dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Gustav,

Following the WITH is the name of the alias being defined, along with its
parameter, and its definition follows the first AS. Now jump down to the
INNER JOIN line, and notice that it's calling itself (i.e. the alias just
defined).

So each row has a menuid and also a parentid. What the JOIN does is link
the child rows to their parent using their parentid to their parent's
menuid. In other words it's a self-join; the table is joined to itself. The
child rows' parentid contains the menuid that identifies its parent row.
Because it's an INNER JOIN, eventually it will fail, in which case you've
traversed the tree all the way to its outermost leaves.

Does that help?

Arthur


On Mon, Oct 28, 2013 at 9:28 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi all
>
> I'm a noob when it comes to T-SQL - try to limit myself to tables and views
> - so could someone please explain what is going on here?
> What does this WITH mean?
> How would you retrieve the data from Access?
> Is "vParent = null" valid code in T-SQL? Why not "vParent is null"?
>
> <SQL>
> WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)
> AS
> (
>     SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.SecurityMenu WHERE
> vParent = null
>     UNION ALL
>     SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM
> dbo.SecurityMenu
>     INNER JOIN security_menu_Recursive AS smr ON smr.menuid =
> dbo.SecurityMenu.vParent
> )
> SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive
> </SQL>
>
> The full tip is here:
>
>
> http://www.codeproject.com/Articles/674287/Recursive-Queries-in-Microsoft-SQ
> L-Server-2008
>
> /gustav
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Arthur