I have 2 tables 1 containing chain

[CrystalReport]

I have 2 tables 1 containing chain & another containing Collection. In short in Chain Table i have one field called Code and another called introcode, & in another Table Called Collection has got 2 fields that containing code & collection, now the thing is that the collection has to go his code & his introcode till in the loop having introcode=code. i.e.

Chain Collection
====== ===========
Code IntroCode Code Collection
101 0 304 1,000
201 101 304 1,500
202 101 303 500
301 201 303 2,000
302 201 302 1,000
303 202 302 1,000
304 202 301 500
201 100
202 500
Now the thing is that i want a sql i.e. if i pass a parameter code=101 then the ouput should be like this.

chain Collection
====== ==========
-101 8,100 i.e. (0[self] + 2,600 + 5,500)
+201 2,600 i.e. (100[self] + 500 + 2,000)
+301 500
+302 2,000
+202 5,500 i.e. (500[self] + 2,500 + 2,500)
+303 2,500
+304 2,500


Note : Please Check this query, it gives me 50% of the answer by dosen't add to the introcode. or eiter it dosen't gives the above output

SELECT a1.code,a2.code,sum(c2.collection),a3.code,sum(c1.collection)
FROM chain a1
FULL OUTER JOIN chain a2
on a1.code=a2.introcode
FULL OUTER JOIN collection c2
on c2.code=a2.code
FULL OUTER JOIN chain a3
on a2.code=a3.introcode
FULL OUTER JOIN certificate c1
on c1.code=a3.code
WHERE a1.code='101'
group by a3.code,a2.code,a1.code


Please give me the solution i have got bugged with this i have heared that drill down can solve my problem, pl. forward me the solution at

pankaj_cash@hotmail.com

from
pankaj.

[sig][/sig]

 

[TomasDill]

Hi!

It is not chain, it is tree:

101
_____| |_____
| |
201 202
___| |___ ___| |___
| | | |
301 302 303 304

And there are no way to query tree-like data in a single query, unless you build additional references table. I will try to explain you why and how to build it.
The main thing you need - recursive-calculating query. However, it is not possible to organize in SQL language in a single query. You might build stored proc that will scan table row by row and build all this, but it will work slowly on large tree. To make a single and quick query you need to organize your data by proper way instead. What is needed is to identify parent, and all parents of parents up to the root level. This should be done for each node. So, your chain table data should be enhanced to following:

Code IntroCode
101 0
201 101
202 101
301 201
302 201
303 202
304 101
* 301 101
* 302 101
* 303 101
* 304 101
401 301
402 301
* 401 201
* 402 201
* 401 101
* 402 101

* - additinal rows

Looks awful, specially when you imagine how much additional rows you will need when you will have long chain (a lot of levels in tree). However, this is good approach because query for such data will look simple and work quickly. Here it is:

select ch.code,
(select isnull(sum(collection.collection),0)
from chain
inner join collection
on collection.code = chain.Code
where chain.IntroCode = ch.code)
+ (select isnull(sum(collection.collection),0)
from collection
where collection.code = ch.Code)
from chain ch
group by ch.code

When you need exact order of result rows by the tree RNN recursive pass-through order (101 - 201 - 301 - 401 - 402 - 302 - 202 - 303 - 304), you need to add additional field to youre tree. For example, you will have result exactly like you need when you add following to your chain table:

Code IntroCode TreeOrder
101 0 1
201 101 2
202 101 5
301 201 3
302 201 4
303 202 6
304 101 7
301 101 3
302 101 4
303 101 6
304 101 7

and put following line in addition to above query:

order by max(ch.TreeOrder)

Hope this helped.
[sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[TomasDill]

Why my tree looks so bad when I posted it? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]