How to write a Query to get the Hierarchical flow

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

I have two tables like Account and Grouptxt for each record in Accounts table i need to find the dependencies in Grouptxt Table,below is the sample data of those 2 tables

Account Table Data:-

#AccountName,ShortName,Type,OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit
'C' Mannto,'C' Mannto,Customer/Supplier,0.0,Debtors Belgaum,True,0.0,0.0
2M Engineers,2M Engineers,Customer/Supplier,260.0,Debtors Belgaum,True,0.0,0.0
A. A. Engineering,A. A. Engineering,Customer/Supplier,114.0,Debtors Belgaum,False,0.0,0.0
A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

Grouptxt Table Data:-

#GroupName,ShortName,ParentGroup,isSystem,IsTradingGroup
Asset, Asset, ,True,False
APPLICATION OF FUNDS,APPLICATION OF FUNDS,Asset,False,False
Fixed Assets,Fixed Assets,APPLICATION OF FUNDS,False,False
Gross Block,Gross Block,Fixed Assets,False,False

So now my Question is for each record in Account tables i need to get the dependencies fromo grouptxt table.How to do that?

the O/P Should be in this format
Expenditure <-- Materials Consumed <--Raw Material & Components A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

For the Record A.C.Enterprises the Dependencies are Raw Material & Components --> Materials Consumed --> Expenditure

like this i need to get for entire table,but not getting how to do that for the entire table

I Tried like this

SQL

SELECT #AccountName
      ,a.ShortName
      ,a.Type
      ,a.OpBal
      ,a.ParentGroup
      ,a.Move4Report
      ,a.DebitLimit
      ,a.CreditLimit
      ,a.row_id,
      g.ParentGroup AS GrpTxt_ParentGroup
FROM Grouptxt   g inner join account a
ON a.ParentGroup=g.ShortName
WHERE #AccountName='Increase/Decrease In Finished Goods'

SELECT * FROM Grouptxt where ParentGroup='Raw Material & Components'

SELECT * FROM Grouptxt Where ShortName='Raw Material & Components'

SELECT * FROM Grouptxt Where ShortName='Materials Consumed'

SELECT * FROM Grouptxt Where ShortName='Expenditure'

Thanks in Advance

Posted 15-Jul-13 20:26pm

Chakri Reddy

Updated 15-Jul-13 23:52pm

Maciej Los

v4

Add a Solution

Comments

Maciej Los 16-Jul-13 3:08am

Please, update your question (using "Improve question" widget) and share with example data for both tables.

Chakri Reddy 16-Jul-13 5:28am

Hi Maciej Los,Thanks for u r reply
i have modified the Question and i had provided Sample data

Raja Sekhar S 16-Jul-13 3:58am

Provide some Sample Data... use Improve Question Widget... Be Specific...

Chakri Reddy 16-Jul-13 5:27am

Hi Raja Sekhar,Thanks for u r reply
I Have Modified the Question and provided sample data.
My Question is
For each row in the Account table expecting the hierarchal flow as shown in the example.

Raja Sekhar S 16-Jul-13 6:36am

What is the Relation Between two tables..?

Chakri Reddy 16-Jul-13 7:08am

ParentGroup of Account table and ShortName of Grouptxt table are similar
i.e;
Account.ParentGroup=Grouptxt.ShortName

Raja Sekhar S 16-Jul-13 7:26am

Remove Where Statement in your Query it will work for entire table....

Chakri Reddy 16-Jul-13 7:36am

thanks for your reply Raja Sekhar

ya it will work for the entire table but it wii display only the records in the accounts table along with the Grouptxt.ParentGroup but what i want is i need the hierarchal flow

i.e for that Grouptxt.ParentGroup whether there is any ParentGroup or not like dat

For Ex Say if i execute the above query i will get one record in which Grouptxt.ParentGroup is
Raw Material & Components again that will have some Parentgroup

Ex:-
Raw Material & Components --> Materials Consumed --> Expenditure like this

Raja Sekhar S 16-Jul-13 7:40am

In that Case Select all Required Columns and use Full Outer Join....
Can u explain What do u mean by Hierarchical Flow Here...?

Chakri Reddy 16-Jul-13 7:59am

Hierarchical Flow Means for Each Record in the Account table i need to get the ParentGroup in Grouptxt Table and i have to find out whether the Record displayed there has any ParentGroup like that it has to continue until it reaches the leaf node where ParentGroup and ShortName of Grouptxt are similar

AS i said in the above post after executing tha above query i will get say o/p like this

#AccountName ShortName Type OpBal ParentGroup Move4Report DebitLimit CreditLimit row_id GrpTxt_ParentGroup GrpTxt_shortName
Increase/Decrease In Finished Goods Increase/Decrease In Finished Goods Other 0 Finished Goods FALSE 0 0 811 Raw Material & Components Finished Goods

hear for "Raw Material & Components" i need to find whether it has any ParentGroup in Grouptxt if it has Say "Materials Consumed" then i need to find its ParentGroup this is what Hierarchical Flow here is

Raja Sekhar S 16-Jul-13 8:07am

In that Case Select all Required Columns and use Full Outer Join....

damodara naidu betha 16-Jul-13 8:42am

Out put format is not clear. Can you make it more clear? and please share data of both the tables.

Chakri Reddy 17-Jul-13 0:52am

Hi Damodara thanks for the reply,

first i will provide the data of Account and Grouptxt tables

Account:-

#AccountName,ShortName,Type,OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit
'C' Mannto,'C' Mannto,Customer/Supplier,0.0,Debtors Belgaum,True,0.0,0.0
2M Engineers,2M Engineers,Customer/Supplier,260.0,Debtors Belgaum,True,0.0,0.0
A. A. Engineering,A. A. Engineering,Customer/Supplier,114.0,Debtors Belgaum,False,0.0,0.0
A.B.Precision Toolings Pvt. Ltd.,A.B.Precision Toolings Pvt. Ltd.,Customer/Supplier,0.0,Other Debtors,False,0.0,0.0
A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A. C. Sales Corporation,A. C. Sales Corporation,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A.D. Steel,A.D. Steel,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A.M. Engineers,A.M. Engineers,Customer/Supplier,260.0,Debtors Pune,True,0.0,0.0
A. N. Kalghatgi,A. N. Kalghatgi,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

Grouptxt:-

#GroupName,ShortName,ParentGroup,isSystem,IsTradingGroup
Asset, Asset, ,True,False
APPLICATION OF FUNDS,APPLICATION OF FUNDS,Asset,False,False
Fixed Assets,Fixed Assets,APPLICATION OF FUNDS,False,False
Gross Block,Gross Block,Fixed Assets,False,False
Depreciation,Depreciation,Fixed Assets,False,False
Net Current Assets,Net Current Assets,APPLICATION OF FUNDS,False,False
Current Assets\u001 Loans and Advances,Current Assets\u001 Loans and Advances,Net Current Assets,False,False
Sundry Debtors,Sundry Debtors,Current Assets\u001 Loans and Advances,False,False
Other Debtors,Other Debtors,Sundry Debtors,False,False

Now the o/p should should be in a format like ,for each record in the account table i need to find the
Hierarchical flow means the Dependencies and the o/p Should be in the below format

Column 1 Column 2 Column 3 Column 4 Column 5
Expenditure Materials Consumed Raw Material & Components

ParentGroup #AccountName ShortName Type
Finished Goods Increase/Decrease In Finished Goods Increase/Decrease In Finished Goods Other
OpBal Move4Report DebitLimit CreditLimit
0.00 FALSE 0.00 0.00

Hear the ParentGroup of Account table is joined with ShortName of Grouptxt table to get the first Dependency
den we will get Raw Material & Components and we have to identify the ParentGroup of Raw Material & Components
like dat

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-07-16T10:02:00

Solution 1

I don't know the exact answer, but it might help you:
Hierarchical and recursive queries in SQL[^]
Hierarchical data using CTE (Common Table Expression) with Depth First Strategy - SQL Server[^]
Managing Hierarchical Data in SQL[^]
Using hierarchyid Data Types (Database Engine)[^]
Recursive Queries Using Common Table Expressions[^]

Posted 16-Jul-13 10:02am

Maciej Los

Comments

Chakri Reddy 17-Jul-13 1:10am

HI Maciej Los thanks for your time and replying to my post

Maciej Los 17-Jul-13 3:41am

You're welcome ;)

Maciej Los 17-Jul-13 3:42am

By The Way: 3 stars means down-voting ;(

Espen Harlinn 17-Jul-13 8:40am

Nice set of links :-D

Maciej Los 17-Jul-13 8:54am

Thank you, Espen ;)