help me query - 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)

help me query

Author

Topic

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:28:27

ollowing is my table structure

sys_id ref_id id Count Kind
1 0 111 12 1
2 0 112 2 1
3 1 115 4 2
4 2 115 3 2
5 0 118 2 1
6 5 120 3 2
7 6 115 6 2

following is sample results i wan't

id Count
115 25((3*6)+3+4)
120 3

and following is sample results i wan't
id Name Count
115 111 4
115 112 3
115 118 18(3*6)
120 118 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 04:31:07

you've two sample outputs shown. which one is actual output you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:33:54

Both of them

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:34:37

both of them

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 04:35:52

ok...can you explain how you got those numbers in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:38:06

both of them

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:38:51

both of them
help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 04:39:43

quote:
Originally posted by niyaz4872

both of them
help me

please first answer the question

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:42:19

both of them

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:43:40

both of them

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:45:00

both of them

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 04:51:26

each number relate to others it mean it s a tree structure let me explain more for example refid shows the father of that node(which sysid show)
for ex:
111 relate to 115
112 relate to 115
118 relate to 120 relate to 115 and .....

Got it?
or Explain More

niyaz4872
Starting Member

41 Posts

Posted - 2011-12-04 : 05:00:13

in first the result i wrote in begining find the id number which equal to 115 then i look at ref id then i return the sys id which equal to this ref id now if kind equal to 1 then i store the count that id (115)
(it equal to 4) and do theses seloution for the other id(whitch it number is 115) so result is this=3+4+(3*6)

(sorry about reapeted post(i made mistake))

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-12-06 : 00:28:36

Using recursive ....

If you need more help, let me know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 00:42:11

quote:
Originally posted by niyaz4872

in first the result i wrote in begining find the id number which equal to 115 then i look at ref id then i return the sys id which equal to this ref id now if kind equal to 1 then i store the count that id (115)
(it equal to 4) and do theses seloution for the other id(whitch it number is 115) so result is this=3+4+(3*6)

(sorry about reapeted post(i made mistake))

i didnt understand how you got 3*6 in calculation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

javad.nikoo
Starting Member

17 Posts

Posted - 2011-12-17 : 00:25:14

i check the ref id and i continue this until i reach 0(ref id )so befrore reach this i will cross count filed and it s exactly recursive method

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources