Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Question Unanswered: need Hierarchical query

    Hi all,
    i want to know how to write a hierarchical query with two table. Here im givig two sample tables which i have taken.

    Asset table1, Issue Table. Please try to help any one.



    Asset table1

    AssetId AstType ParentAssetId

    1 1 NULL
    2 2 1
    3 3 2
    4 4 3
    5 4 3
    6 4 3
    7 4 3
    8 5 4
    9 7 4
    10 5 4
    11 6 8
    12 6 8
    13 6 8
    14 6 8
    15 8 9
    16 8 9
    17 9 15
    18 9 15
    19 7 5
    20 7 5
    21 7 5
    22 7 5
    23 5 6
    24 5 6
    25 5 6
    26 5 6
    27 7 7
    28 5 7
    29 5 7
    30 10 19
    31 10 19
    32 10 19
    33 10 19
    34 10 19
    35 10 19
    36 10 19
    37 10 20
    38 10 20
    39 10 20
    40 10 20
    41 10 20
    42 10 20
    43 10 20
    44 10 20
    45 10 20
    46 10 20
    47 10 20
    48 10 22
    49 10 22
    50 10 22
    51 10 22
    52 10 22
    53 8 22
    54 10 22
    55 10 22
    56 8 22
    57 10 22
    58 10 22
    59 10 22
    60 10 22
    61 8 22
    62 10 22
    63 10 22
    64 10 22
    65 6 24
    66 6 24
    67 6 24
    68 6 24
    69 6 24
    70 6 24
    71 6 24
    72 6 24
    73 6 24
    74 6 24
    75 10 27
    76 10 27
    77 10 27
    78 8 27
    79 10 27
    80 10 27
    81 10 27
    82 10 27
    83 10 27
    84 10 27
    85 10 27
    86 10 27
    87 10 27
    88 10 27
    89 10 27
    90 10 27
    91 10 27
    92 10 27
    93 6 28
    94 6 28
    95 6 28
    96 6 28
    97 6 28
    98 6 28
    99 6 28
    100 6 28
    101 6 28
    102 6 28
    103 6 29
    104 6 29
    105 6 29
    106 9 78
    107 9 78
    108 9 78
    109 3 2
    122 3 2
    124 4 122
    126 4 109
    127 5 126
    130 4 122
    131 4 109
    133 3 2
    135 4 133
    138 4 133
    139 3 2
    140 4 139
    141 4 139
    143 16 11
    144 16 11
    145 16 11
    146 16 11
    147 12 143
    148 12 143
    149 13 152
    150 13 144
    151 16 13
    152 16 13
    153 16 13
    154 16 13
    155 16 13
    156 13 144
    157 13 152
    158 4 139
    159 4 139


    Issue tbale is:

    IssueId AssetId

    5 2
    7 122
    12 2
    13 2
    14 2
    15 2
    17 138
    18 2
    19 135
    20 2
    22 2
    25 4
    26 8
    27 149
    31 10
    33 140

    Now i want to Count of IssueId in the tree of AssetId From Asset table and Issue table.

    I will explain how 1, 2 and 3 are having, 1 is parent for all AssetId comes under 1, 2 is child for 1 and parent for remaining all, 2 already having 8 IssueIds directly in Issue table and their childs are 4, 8, 10, 122, 135, 138, 140 and 149. 4 is child for 2 and 1, its having directly one issue, and also parent for 8, 10, 149. like we need to count all issues from IssueTable and which has to map with asset table.

    Expected Output is:

    AssetId CountOfIssueId
    1 16
    2 16
    3 4
    4 4
    5 0
    6 0
    7 0
    8 2
    9 0
    10 1
    11 0
    12 0
    13 1
    14 0
    15 0
    16 0
    17 0
    18 0
    19 0
    20 0
    21 0
    22 0
    23 0
    24 0
    25 0
    26 0
    27 0
    28 0
    29 0
    30 0
    31 0
    32 0
    33 0
    34 0
    35 0
    36 0
    37 0
    38 0
    39 0
    40 0
    41 0
    42 0
    43 0
    44 0
    45 0
    46 0
    47 0
    48 0
    49 0
    50 0
    51 0
    52 0
    53 0
    54 0
    55 0
    56 0
    57 0
    58 0
    59 0
    60 0
    61 0
    62 0
    63 0
    64 0
    65 0
    66 0
    67 0
    68 0
    69 0
    70 0
    71 0
    72 0
    73 0
    74 0
    75 0
    76 0
    77 0
    78 0
    79 0
    80 0
    81 0
    82 0
    83 0
    84 0
    85 0
    86 0
    87 0
    88 0
    89 0
    90 0
    91 0
    92 0
    93 0
    94 0
    95 0
    96 0
    97 0
    98 0
    99 0
    100 0
    101 0
    102 0
    103 0
    104 0
    105 0
    106 0
    107 0
    108 0
    109 0
    122 1
    124 0
    126 0
    127 0
    130 0
    131 0
    133 2
    135 1
    138 1
    139 1
    140 1
    141 0
    143 0
    144 0
    145 0
    146 0
    147 0
    148 0
    149 1
    150 0
    151 0
    152 1
    153 0
    154 0
    155 0
    156 0
    157 0
    158 0
    159 0







    Thanks
    VKR

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server are you using?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •