One Interesting Query!. Please Help

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 2000 Forums

Transact-SQL (2000)

One Interesting Query!. Please Help

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-30 : 06:30:39

Salute..
I have the following two tables


SectionsTable
----------------
SectionId   ParentSectionId   SectionName
---------   ---------------   -----------
1           0                 Aaa
2           1                 X
3           1                 Y
4           0                 Bbb
5           4                 G
6           4		      H

And

AccountTable
---------------
TransId    SectionId         AccountNo
--------   ---------         ---------
1          2                 6
2          2                 7
3          3                 8
4          3                 9

Now..
How can I write the SELECT statement that selects the sections and the accountno that belong to them....for example


SectionId   AccountNo
---------   ---------
1           6   (becasue section 1 is the parent of section 2 and 3)
1           7   (becasue section 1 is the parent of section 2 and 3)
1           8   (becasue section 1 is the parent of section 2 and 3)
1           9   (becasue section 1 is the parent of section 2 and 3)
2           6
2           7
3           8
3           9
etc.....

Thanks ALOT

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-30 : 08:19:59

first off - shouldn't there be a section 0 , owning 6,7,8,9 and more since it owns 1 and 4, and 1 owns 2 and 3, while 4 owns 5 and 6 ... OR is 0 a value when there is no parent ?

secondly, you list account 6 being owned by 1 and 2 ... is that correct , or do you want the highest level owner, i.e 1, not 2 , for account 6 ( or 0 if it is a valid section - see Q1 above).

CiaO

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

BigRetina
Posting Yak Master

144 Posts

Posted - 2003-06-30 : 13:23:41

0 is a value when there is no parent.
second i want every section and what it owns of accounts either directly or indirectly
Thanks

Nazim
A custom title

1408 Posts

Posted - 2003-07-01 : 02:21:33

Rob has written a excellent article on such issues. read this http://sqlteam.com/item.asp?ItemID=8866 .

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources