Tree Search in SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Tree Search in SQL

Author

Topic

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 07:55:35

Hi,
I have a DB looks like:

Accounts:
+ AccountID
+ AccountName
+ ParentID

I want to search all the Accounts under Parent (but parent has also more parents) so
Example: i have an accountid and i want to find is there any other accounts under the same Parent. How can i search it?

Thanks

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 08:08:16

Use a recursive query: (sql 05/08)
WITH accountsUnderParent AS
(
SELECT AccountId, ParentId
FROM Accounts WHERE AccountId = @AccountId
UNION ALL
SELECT tbl1.ParentId, tbl2.AccountId
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.ParentId = tbl2.AccountId
)

SELECT AccountId, ParentId
FROM accountsUnderParent

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:25:16

But than i am getting some errors:

Must declare the scalar variable "@AccountID".
and
Incorrect syntax near ')'.

and i want to see all the children from one parentaccount?

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:32:05

and for example a parent has 3 children in UK and the other ones in Belgien and bla bla bla, i want to see from one parent where are all children which are in UK?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 08:32:11

I don't have the SQL server open so I can't test it.

The @AccountID is a parameter, just replace it with the ID of the id of the parent account you want to search.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 08:33:27

quote:
Originally posted by xmux

and for example a parent has 3 children in UK and the other ones in Belgien and bla bla bla, i want to see from one parent where are all children which are in UK?

Use a recursive query: (sql 05/08)
WITH accountsUnderParent AS
(
SELECT AccountId, ParentId, Location
FROM Accounts WHERE AccountId = @AccountId
UNION ALL
SELECT tbl1.ParentId, tbl2.AccountId, Location
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.ParentId = tbl2.AccountId
)

SELECT AccountId, ParentId
FROM accountsUnderParent

I don't know your data structure but assuming that there is a location column in the accounts, I modified the query above to contain the location column.

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:40:23

ok i got now!
but than i got another error :)
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

do u have any idea what it can be?

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:41:25

i wrote exactly this one:
WITH accountsUnderParent AS
(
SELECT cmp_code, Parent
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'
)

SELECT cmp_code, Parent
FROM accountsUnderParent

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 08:52:56

SELECT Parent, cmp_code, cmp_fctry
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'

In a UNION query, both queries MUST have the same exact count of return columns. In the top query, you haven't specified cmp_fctry as a return column...I fixed it above.

EDIT: Also, the order of the columns is wrong.....you got the Parent first in the second query, but in the first query you have cmp_code. Fixed above.

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:57:52

but what is this accountsUnderParent, i dont have any table like this..

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 08:58:08

by the way thanks for helping!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 09:05:55

quote:
Originally posted by xmux

but what is this accountsUnderParent, i dont have any table like this..

accountsUnderParent is a temp table.

WITH accountsUnderParent AS ----- Creates the temp table accountsUnderParent and fills it with the following data from the UNION query below
(
SELECT cmp_code, Parent
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'
)

SELECT cmp_code, Parent
FROM accountsUnderParent

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 09:10:27

ok but i am getting the same error again i tried with temp table

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Why it can be?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 09:12:35

Make sure the union query looks like this:

SELECT Parent, cmp_code, cmp_fctry
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'

Notice that they are both returning 3 columns. In the original query, your first query was returning 2 columns and your second column was returning 3 columns. For a union query, both queries must return the same number of columns.

xmux
Starting Member

10 Posts

Posted - 2009-04-17 : 09:20:27

WITH accountsUnderParent AS
(
SELECT Parent, cmp_code, cmp_fctry
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'
)

SELECT cmp_code, Parent
FROM accountsUnderParent

this is the query i am writing right now but than i am getting another error :
Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'cmp_fctry'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'cmp_fctry'.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-17 : 09:24:28

WITH accountsUnderParent AS
(
SELECT Parent, cmp_code, cmp_fctry
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, tbl2.cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND cmp_fctry = 'UK'
)

Noticed I added tbl2.cmp_fctry in the second query

xmux
Starting Member

10 Posts

Posted - 2009-04-20 : 06:17:59

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 06:30:50

[code]
WITH accountsUnderParent AS
(
SELECT Parent, cmp_code, cmp_fctry
FROM Accounts WHERE cmp_code = 161946
UNION ALL
SELECT tbl1.Parent, tbl2.cmp_code, tbl2.cmp_fctry
FROM accountsUnderParent AS tbl1, Accounts AS tbl2
WHERE tbl1.Parent = tbl2.cmp_code
AND tbl2.cmp_fctry = 'UK'
)

SELECT cmp_code, Parent
FROM accountsUnderParent
[/code]

Also : Why are you using that syntax in the where clause. You should replace it with a proper ANSI JOIN

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources