August 9, 2012 at 12:42 pm
I have 2 tables. One table contains all parent accounts, top level of hierarchy. Second table has all children accounts, that may or may not have a match to a parent account in the parent table. The goal is to create a query (SQL Server 2008, recursive or non) that finds all child accounts that match to the parent in addition to the fact that the child could itself be a parent to other child accounts.
In simpler terms, once a match has been made on a parent to child, need to check to make sure that the child in the match is not itself a parent to other child accounts. A mouthful I understand and I hope it makes sense. I also do not know the depth of which the hierarchy would extend.
CREATE TABLE dbo.Parent_Accounts
(Parent_Account_Key_Lookup varchar(28) NOT NULL,
Account_Number bigintNOT NULL,
Reference_Account_Number_1 bigintNOT NULL,
Reference_Account_Number_2 bigintNOT NULL,
OpenDate intNOT NULL,
Status char(1) NOT NULL,
Record_Created smalldatetimeNOT NULL,
Active bitNOT NULL)
GO
CREATE TABLE dbo.Child_Accounts
(Child_Account_Key_Lookup varchar(28) NOT NULL,
Account_Number bigintNOT NULL,
Reference_Account_Number_1 bigintNOT NULL,
Reference_Account_Number_2 bigintNOT NULL,
OpenDate intNOT NULL,
Status char(1) NOT NULL,
Record_Created smalldatetimeNOT NULL,
Active bitNOT NULL)
GO
WITH cte_Recursive
AS(SELECT parent.Account_Number,
parent.Parent_Account_Key_Lookup,
parent.Reference_Account_Number_1,
parent.Reference_Account_Number_2,
parent.OpenDate,
parent.[Status],
parent.Record_Created,
parent.Active,
1 AS Hierarchy_Level
FROM dbo.Parent_Accounts parent
WHERE parent.Account_Number = 4498481055218674
UNION ALL
SELECT child.Account_Number,
child.Child_Account_Key_Lookup,
child.Reference_Account_Number_1,
child.Reference_Account_Number_2,
child.OpenDate,
child.[Status],
child.Record_Created,
child.Active,
cte.Hierarchy_Level + 1
FROM cte_Recursive cte
INNER JOIN dbo.Child_Accounts child
ON cte.Parent_Account_Key_Lookup = child.Child_Account_Key_Lookup)
--SELECT * FROM cte_Recursive
SELECT TOP 2 * FROM cte_Recursive
INSERT INTO dbo.Parent_Accounts
(Parent_Account_Key_Lookup,
Account_Number,
Reference_Account_Number_1,
Reference_Account_Number_2,
OpenDate,
[Status],
Record_Created,
Active)
VALUES ('222248105521867419970702', 2222481055218674, 2222481060975466, 0, 19970702, 'U', '2010-11-18 12:46:00', 0)
INSERT INTO dbo.Child_Accounts
(Child_Account_Key_Lookup,
Account_Number,
Reference_Account_Number_1,
Reference_Account_Number_2,
OpenDate,
[Status],
Record_Created,
Active)
VALUES ('222248105521867419970702', 2222481060975466, 2222481055218674, 2222481055218674, 19970702, 'L', '2010-11-19 08:33:00', 0),
('222248106097546619970702', 2222481060982900, 2222481060989137, 2222481060975466, 19970702, 'U', '2010-11-19 16:54:00', 0),
('222248106098290019970702', 2222481060989137, 0, 2222481060982900, 19970702, ' ', '2010-11-21 01:52:00', 1)
If you run each of the select statements from the cte you will see the issue. I have included an attachment of what I am trying to accomplish.
August 9, 2012 at 1:55 pm
Question:
Do you have to have two tables? Is it possible for child accounts to have more than one parent?
Erin
August 9, 2012 at 1:58 pm
I do not have to have 2 tables. Was just to keep my sanity. Answer to other question was yes.
August 9, 2012 at 2:18 pm
Let me understand this.. you have a many-to-many relationship (parent-child and child-parent) and you'd like to pull a list of all parents and their children only, no grandchildren.
August 9, 2012 at 2:21 pm
Yes. I believe so.
August 9, 2012 at 2:23 pm
Does the attachment I included make sense. I am trying to track how an account number changes over time, but as they do change there is still a root account that they all derived from.
August 9, 2012 at 2:24 pm
With the example you show in the attachment I don't see how a single account can have multiple parents. If it's not possible for an account to have multiple parent accounts concurrently then I would simply add a hierarchy column to one table to show which parent a child is associated.
August 9, 2012 at 2:29 pm
Determined from what table the account number was selected from, parent or child. If I am looking at this incorrectly, please let me know as this has me baffled.
August 9, 2012 at 3:43 pm
Good advice, Celko. I'm just hoping this isn't an architecture that he's inherited and can make some structural changes or additions.
Erin
August 9, 2012 at 3:57 pm
I am open to suggestions. Could you provide an example please? Nothing fancy just an idea.
August 9, 2012 at 7:21 pm
Can you provide some more sample data of the following?
PARENT
/ CHILD1 CHILD2
/ \ / CHILD3 CHILD4CHILD5CHILD6
August 9, 2012 at 7:37 pm
I can get you started but I have a question. Your rCTE looked close but I've made some modifications that I'll explain in a minute. First a revised data setup to use temp tables (my preference):
CREATE TABLE #Parent_Accounts
(Parent_Account_Key_Lookup varchar(28) NOT NULL,
Account_Number bigint NOT NULL,
Reference_Account_Number_1 bigint NOT NULL,
Reference_Account_Number_2 bigint NOT NULL,
OpenDate int NOT NULL,
Status char(1) NOT NULL,
Record_Created smalldatetime NOT NULL,
Active bit NOT NULL)
CREATE TABLE #Child_Accounts
(Child_Account_Key_Lookup varchar(28) NOT NULL,
Account_Number bigint NOT NULL,
Reference_Account_Number_1 bigint NOT NULL,
Reference_Account_Number_2 bigint NOT NULL,
OpenDate int NOT NULL,
Status char(1) NOT NULL,
Record_Created smalldatetime NOT NULL,
Active bit NOT NULL)
INSERT INTO #Parent_Accounts
(Parent_Account_Key_Lookup,
Account_Number,
Reference_Account_Number_1,
Reference_Account_Number_2,
OpenDate,
[Status],
Record_Created,
Active)
VALUES ('222248105521867419970702', 2222481055218674, 2222481060975466, 0, 19970702, 'U', '2010-11-18 12:46:00', 0)
INSERT INTO #Child_Accounts
(Child_Account_Key_Lookup,
Account_Number,
Reference_Account_Number_1,
Reference_Account_Number_2,
OpenDate,
[Status],
Record_Created,
Active)
VALUES ('222248105521867419970702', 2222481060975466, 2222481055218674, 2222481055218674, 19970702, 'L', '2010-11-19 08:33:00', 0),
('222248106097546619970702', 2222481060982900, 2222481060989137, 2222481060975466, 19970702, 'U', '2010-11-19 16:54:00', 0),
('222248106098290019970702', 2222481060989137, 0, 2222481060982900, 19970702, ' ', '2010-11-21 01:52:00', 1)
SELECT * FROM #Parent_Accounts
SELECT * FROM #Child_Accounts
Now, in my version of the rCTE (below), I've made the anchor leg of the rCTE match parent accounts (from that table) with child accounts.
;WITH cte_Recursive AS (
-- Anchor leg (1): List out all the parents with children
SELECT p.Account_Number,
p.Parent_Account_Key_Lookup,
p.Reference_Account_Number_1,
p.Reference_Account_Number_2,
p.OpenDate,
p.[Status],
p.Record_Created,
p.Active,
1 AS Hierarchy_Level
FROM #Parent_Accounts p
INNER JOIN #Child_Accounts c
ON p.Parent_Account_Key_Lookup = c.Child_Account_Key_Lookup
--UNION ALL
--SELECT c.Account_Number,
-- c.Child_Account_Key_Lookup,
-- c.Reference_Account_Number_1,
-- c.Reference_Account_Number_2,
-- c.OpenDate,
-- c.[Status],
-- c.Record_Created,
-- c.Active,
-- p.Hierarchy_Level + 1
--FROM cte_Recursive p
--INNER JOIN #Child_Accounts c
-- ON p.Parent_Account_Key_Lookup = c.Child_Account_Key_Lookup
)
SELECT *
FROM cte_Recursive
DROP TABLE #Parent_Accounts
DROP TABLE #Child_Accounts
The recursive leg (commented out) needs to match any child account that itself has a child. What I couldn't figure out from your data is exactly how that relationship is reckoned. This may require two recursive legs - the first to resolve the initial parents and then the second to resolve children of the child parents.
Did this help?
Ya gotta love dem recursive CTEs!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 7:43 pm
I am intrigued. How do you build two recursive legs within one cte? You have my attention.
August 9, 2012 at 7:47 pm
;WITH rCTE AS (
-- Anchor 1: ...
SELECT ...
UNION ALL
-- Anchor 2: ...
SELECT ...
UNION ALL
-- Recursive 1: ...
SELECT ...
UNION ALL
-- Recursive 2: ...
SELECT ...
)
SELECT * FROM rCTE
You can have as many anchor and recursive legs as you need... all separated by UNION ALL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 7:48 pm
If Reference_Account_Number_2 is 0 this would be a parent account as there are no other accounts associated with it at this point. A child account would be an account number was populated with an account number. I tried to depict that with my image that is attached to the post. I am able to get the first 2 levels of the hierarchy however it is when the account number needs to become a parent to again check to see if any other accounts are attached. A daisy chain if you will.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply