Searching in a tree - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching in a tree

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-06-06 : 15:50:37

Hi,
I’ve this table called Company where I’ve CompanyID, ParentCompanyID and other columns.
I built a tree of the companies based on CompanyID and ParentCompanyID. So I used the following query:


WITH Companies(ParentCompanyID, CompanyID, CompanyName, Level)
AS (
SELECT a.ParentCompanyID , a.[CompanyID],a.[CompanyName], 0 as Level
FROM [dbo].[Company] a 
WHERE ParentCompanyID = 0 
UNION ALL
SELECT a.[ParentCompanyID], a.[CompanyID],a.[CompanyName], Level + 1
FROM [dbo].[Company] a 
INNER JOIN Companies AS b ON
a.ParentCompanyID = b.CompanyID
 )
SELECT CASE ParentCompanyID WHEN 0 THEN  NULL 
ELSE ParentCompanyID END AS ParentCompanyID, 
CompanyID, CompanyName, Level
FROM Companies
Order By CompanyName

Now - I need to build a query for searching by CompanyName like
1) When a child company is a match, it will return only the tree of immediate parents. Like:
A has children P,Q,R.
Q has children S,T.
R has children X,Y,Z.
If there's a search for Y - it'll return only Y, R and A.
2) If a Parent is a match - then it will return the whole tree under the parent, plus the immediate parents of this parent. So -
If A is a match - it will return A, P,Q,R, S,T, X,Y,Z.
If Q is a match, it will return A (parent of parent), Q, S,T.

Need help please.
Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 16:05:50

For searching up the tree use this:

DECLARE @companyName VARCHAR(32);
;WITH cte AS
(
	SELECT * FROM Company WHERE @companyName = CompanyName
	UNION ALL
	SELECT * FROM Company c
	INNER JOIN cte t ON c.ParentCompanyId = t.CompanyId
)
SELECT * FROM cte;

For searching down the tree, change the join on the recursive part of the cte to

INNER JOIN cte t ON c.CompanyId = t.ParentCompanyId

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-06-06 : 16:38:47

Hi James, Thanks for your help.
There's a problem in the query though.
I get "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."
Error, and I don't see any reason for the error.
Any ideas?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 17:22:38

Sorry about that; Add a "c." shown in red below:

DECLARE @companyName VARCHAR(32);
;WITH cte AS
(
	SELECT * FROM Company WHERE @companyName = CompanyName
	UNION ALL
	SELECT c.* FROM Company c
	INNER JOIN cte t ON c.ParentCompanyId = t.CompanyId
)
SELECT * FROM cte;

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-06-06 : 17:39:03

Thanks James, wonderful. It works great.
You are awesome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources