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