Thursday, December 19, 2013

TSQL - How to use OUTER JOINs in a recursive CTE

Recently I was bummed out by SQL Server telling me that outer joins are strictly prohibited inside of a recursive CTE!
Outer join is not allowed in the recursive part of a recursive common table expression 'children'.

Bummer!
What's a developer to do?
I really need that non-inner join!
Do I really have to do this thing manually?!
But the recursive CTE expression is so neat and concise!

In your moment of desperation, the answer comes!
The answer?
Use a view!

Now, to make an example out of SQL Server!

You will do what I tell you to do!
Here's how this example works:
I have a table that holds the tree nodes.  It has TreeId, PrarentId, and Name fields.
The root is identified by having the ParentId being null.

I have another table that holds extra details about a node, like description.  Not every tree node has an entry in this table, so if we want to join to this table, it must not be an INNER JOIN!

Okay, now we need some setup code to test with!

Setup Code

The Tables

USE [Test]
GO
/****** Object:  Table [dbo].[tblTree]    Script Date: 12/19/2013 11:00:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTree](
 [TreeId] [int] IDENTITY(1,1) NOT NULL,
 [ParentId] [int] NULL,
 [Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_tblTree] PRIMARY KEY CLUSTERED 
(
 [TreeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblTreeExtraInformation]    Script Date: 12/19/2013 11:00:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTreeExtraInformation](
 [TreeId] [int] NOT NULL,
 [Description] [nvarchar](2000) NULL,
UNIQUE NONCLUSTERED 
(
 [TreeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTreeExtraInformation]  WITH CHECK ADD  CONSTRAINT [FK_tblTreeExtraInformation_tblTree] FOREIGN KEY([TreeId])
REFERENCES [dbo].[tblTree] ([TreeId])
GO
ALTER TABLE [dbo].[tblTreeExtraInformation] CHECK CONSTRAINT [FK_tblTreeExtraInformation_tblTree]
GO

The Test Data
set identity_insert tblTree on

insert into tblTree (TreeId, ParentId, Name)
values (1, null, 'Root')
   ,(2, 1, 'A')
   ,(3, 2, 'A 1')
   ,(4, 3, 'A 2')
   ,(5, 1, 'B')
   ,(6, 5, 'B 1')
   ,(7, 6, 'B 2')
   ,(8, 7, 'B 3')
   ,(9, 7, 'B 3.1')

set identity_insert tblTree off

insert into tblTreeExtraInformation (TreeId, Description)
        values (1, 'This is the root node')
           ,(2, 'A is for Awesome God!')
           ,(5, 'B is for Brandon');

The Test Script
(The broken version!)
with children (Level, TreeId, ParentId, Name, Description) as (
select 1, t.TreeId, t.ParentId, t.Name, tei.Description from tblTree t left outer join tblTreeExtraInformation tei on (t.TreeId = tei.TreeId) where t.ParentId is null
union all
select c.Level + 1, t.TreeId, t.ParentId, t.Name, tei.Description
from
 children c inner join
 tblTree t on (t.ParentId = c.TreeId) left outer join
 tblTreeExtraInformation tei on (t.TreeId = tei.TreeId)
)

select * from children;

Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common table expression 'children'.

Check Out The View
USE [Test]
GO

/****** Object:  View [dbo].[view_TreeFull]    Script Date: 12/19/2013 11:21:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_TreeFull]
AS
SELECT     t.TreeId, t.ParentId, t.Name, tei.Description
FROM         dbo.tblTree AS t LEFT OUTER JOIN
                      dbo.tblTreeExtraInformation AS tei ON t.TreeId = tei.TreeId
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "t"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 110
               Right = 198
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tei"
            Begin Extent = 
               Top = 6
               Left = 236
               Bottom = 95
               Right = 396
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_TreeFull'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_TreeFull'
GO


Using the New View
with children (Level, TreeId, ParentId, Name, Description) as (
select 1, TreeId, ParentId, Name, Description from view_TreeFull tf where tf.ParentId is null
union all
select c.Level + 1, tf.TreeId, tf.ParentId, tf.Name, tf.Description
from
 children c inner join
 view_TreeFull tf on (c.TreeId = tf.ParentId)
)

select * from children;














Mission Accomplished!

This is my first blog post, so definitely leave a comment on how I can improve!

My name is Brandon Miller and I work closely with .NET and SQL Server.  I do find myself doing quite a bit of UI work on both the desktop and web.
I am a Christian and love to share the Gospel with anyone who will listen!
My life was a complete mess before I came to know Christ.  I have made a lot of decisions in my life and I can say that inviting Jesus into my heart was the best choice that I have ever made.
Here's why:
God created us and we were perfect.
God's perfect creation, man, was tempted by Satan and sinned.
God is 100% just and cannot allow unjust behavior go unpunished.
God also loves us so much that, guess what?
He loved us so much that He sent His only Son here to take the punishment of OUR sins.
He never sinned Himself, yet He paid the penalty for us.
Do you know what would happen if He didn't do that?
Every one of us would be sentenced to Hell because every one of us broke God's law.
Don't be fooled--Hell is the most miserable place you can ever come in contact with.  There will be no parties with friends and all is fun.
God loves us so much that He made it possible for us to get right with Him.
He sent Jesus Christ into this world.  He died a most cruel death.  Then, three days later, God raised Jesus from the dead!  That's right!  He's alive right now in Heaven with the Father!
Unless we believe in God's Son, Jesus Christ, we will still go to Hell.
That's 100% true.  Jesus is the only way to Heaven.  There is no other way.  Don't even bother trying to fool yourself.  Nobody is good enough to get into Heaven on their own.  If you don't believe in Jesus, your future is most miserable.
Why don't you accept Jesus into your heart right now?
It doesn't take long and it will be the best decision you will ever make!
1. You must know that you're a sinner.  Identify at least one sin that you committed.  Realize that one sin alone would send you to Hell because God is 100% just--He demands justice, and you will pay with your soul
2. You must realize that there is nothing you can do to "earn" your salvation (life with God in Heaven).  It doesn't matter if you sell everything you have and give it to the poor--you're still going to Hell.  You're absolutely powerless to save yourself.
3. You must believe that Jesus Christ came from Heaven, lived a sinless life, died for your sins, and was raised to life by God on the third day after his death and now sits on the right hand of the Father in Heaven.
4. You must repent of your sinful life!  You cannot go on thinking everything is alright sinning!  If you think you can, you've missed what it truly means to believe in Jesus.  Don't worry, the moment you accept Jesus into your heart, the Holy Spirit lives in your body.  It will instruct you in God's ways and lead you into righteousness.
5. Thank Jesus for dying on the cross for your sins.  Thank God for sending Jesus, His Son!  That was an amazing sacrifice for God--thank Him for it.  It is the natural reaction when you truly repent.

Remember, God loves you very much!
If you truly receive Christ into your heart, you are now in God's family, a brother to Jesus Christ, and a child of God!

Be sure to get in the Word of God (the Bible is the written Word of God) on a daily basis.
I recommend starting with John: http://www.biblegateway.com/passage/?search=John%201&version=NLT

If this has helped you, please let me know!

Be blessed!
Brandon