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
Thanks Brandon. I was able to get my statement working with the view.
ReplyDelete