Need SQL Query for Hierarchy
-
Hi there, I need some t-sql query help. My problem is this. We have a table name customer and one of it's column is parent_id. The colum "parent_id" tells if the current customer has a parent customer (and so on). If the value is either null or 0 it has no parent. What I need I think is like a recursive query to determine all my children and I need a pure T-Sql query because we will be passing this query thru a JDBC driver inside a java application.
The partial structure of table is this
Table name: customer
id int
name varchar(50)
parent_id int
Your help is greatly appreciated.
Thanks
-
It must be table UDF accepting @CustomerId as a parameter and collecting ID's for all children using recursive search on table Customer.
After all you use
SELECT * FROM dbo.CustomerChildren(@SomeID)
You may join it to any query as usual table.
_____________
Code for TallyGenerator -
Ya but you can't join using a column as a parameter (unless you use sql 2005 where the cross apply operator becomes your friend
). -
Warning: Recursion is good only to 32 levels and then BOOM!!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why?
Once I forgot to change IDs to newly retrieved and it was retrieving same set again and again.
When I stopped it there were couple of millions recursive calls completed.
And tempdb was really stuffed.
But it was not stopped without my intrusion. It would go until the disk space is exhaysted.
_____________
Code for TallyGenerator -
You can have millions of recursive calls so long as you don't exceed 32 levels of nesting... same as number of nested triggers, nested stored procedures, etc. Write a function that calls itself, say, 40 times... at 33rd call (33rd level deep), you'll get the error message about recursive levels not allowed for more than 32 levels.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh, what is this about...
There are no recursive calls in that function.
Just simple WHILE loop addressing table variable.
So, no one can stop me if I make a mistake!
_____________
Code for TallyGenerator -
That'll do... I was just worried about the fact that you said it uses "recursive search on Customer table". To me, recursion is when a proc or function calls itself... not loops as you say although I also used to call loops a form of recursion until my old SQL mentor hit me in the head with a bat 10 years ago... yeah, that explains a lot, doesn't it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm not sure what results you're seeking, what would your results look like if you suceeded?
-
Jun,
Crud... Dan's right... sorry we got off track...
To reiterate Dan's question, what should the output look like?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Guys thanks for all your response. Answer to the question by Dan is ...
Here is a data sample:
1. customer "WALMART CORPORATE" with id = 1 AND parent_id = null
2. customer "WALMART WESTCOST" with id = 10 AND parent_id = 1
3. customer "WALMART CALIFORNIA" with id = 11 AND parent_id = 1
4. customer "WALMART OREGON" with id = 12 AND parent_id = 1
5. customer "WALMART WASHINGTON" with id = 13 AND parent_id = 1
6. customer "WALMART SEATTLE" with id = 1301 AND parent_id = 13
7. customer "WALMART LYNNWOOD" with id = 1302 AND parent_id = 13
8. customer "WALMART BELLEVUE" with id = 1303 AND parent_id = 13
9. customer "WALMART OLYMPIA" with id = 1304 AND parent_id = 13
10. customer "WALMART REDMOND" with id = 1305 AND parent_id = 13
Use Case: I want to return myself and all my children
Scenario #1: customer.id = 1 (WALMART CORPORATE)
This should return all 10 rows above.
Scenario #1: customer.id = 13 (WALMART WASHINGTON)
This should return the following data:
5. customer "WALMART WASHINGTON" with id = 13 AND parent_id = 1
6. customer "WALMART SEATTLE" with id = 1301 AND parent_id = 13
7. customer "WALMART LYNNWOOD" with id = 1302 AND parent_id = 13
8. customer "WALMART BELLEVUE" with id = 1303 AND parent_id = 13
9. customer "WALMART OLYMPIA" with id = 1304 AND parent_id = 13
10. customer "WALMART REDMOND" with id = 1305 AND parent_id = 13
Scenario #1: customer.id = 1301 (WALMART SEATTLE)
This should return the following data:
6. customer "WALMART SEATTLE" with id = 1301 AND parent_id = 13
As you can see from the sample data, that the "WALMART CORPORATE" is the parent of all "WALMART ?????" rows or stores.
Hope this sample answer dan's questions. Also, I can not use store procedures or UDF for this matter (business requirement). If it is only possible to create a single query or few, that will be good.
-
WTH? Why can't you use stored procedures or a UDF? Yeah, I know... "Business Requirement". WHAT is that business requirement?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This should pass your Use Cases...
--=============================================================================
-- Setup for testing
--=============================================================================
--===== System presets
SET NOCOUNT ON
--===== If the temporary test table exists, drop it
IF OBJECT_ID('TempDB..#Customer') IS NOT NULL
DROP TABLE #Customer
--===== Create the temporary test table
CREATE TABLE #Customer
(
ID INT PRIMARY KEY CLUSTERED,
Parent_ID INT,
Name VARCHAR(100)
)
--===== Populate the temporary test table
INSERT INTO #Customer
(Name,ID,Parent_ID)
SELECT 'WALMART CORPORATE' , 1, NULL UNION ALL
SELECT 'WALMART WESTCOST' ,10, 1 UNION ALL
SELECT 'WALMART CALIFORNIA',11, 1 UNION ALL
SELECT 'WALMART OREGON' ,12, 1 UNION ALL
SELECT 'WALMART WASHINGTON',13, 1 UNION ALL
SELECT 'WALMART SEATTLE' ,1301,13 UNION ALL
SELECT 'WALMART LYNNWOOD',1302,13 UNION ALL
SELECT 'WALMART BELLEVUE',1303,13 UNION ALL
SELECT 'WALMART OLYMPIA' ,1304,13 UNION ALL
SELECT 'WALMART REDMOND' ,1305,13
--=============================================================================
-- Demo possible solution
--=============================================================================
--===== If the results table exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
--===== Create the results table with row numbers to preserve the order
CREATE TABLE #Results
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ID INT,
Parent_ID INT,
Name VARCHAR(100)
)
--===== Create and preset the variable to hold the ID to find the downline for
DECLARE @SeedID INT --Could be a parameter for a stored proc or UDF rable variable
SELECT @SeedID = 1301 --<<LOOK!!! This is what you change for your USE cases!!!
--===== Seed the results table
INSERT INTO #Results (ID,Parent_ID,Name)
SELECT ID,Parent_ID,Name
FROM #Customer
WHERE ID = @SeedID
--===== Step through the hierarchy and return rows in order
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO #Results
(ID,Parent_ID,Name)
SELECT c.Id ,c.Parent_ID,c.Name
FROM #Results i
INNER JOIN #Customer c
ON c.Parent_ID = i.ID
LEFT OUTER JOIN #Results i1
ON i1.Id = c.Id
WHERE i1.Id IS NULL
END
--===== Display the results
SELECT * FROM #Results
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply