T- SQL Inner Select
-
There is two tables HP_AccountName and T_Values. HP_AccountName includes all main accounts,T_Values includes all operations about accounts. Sample Tables r fallowing. HP_AccountName............................|T_Values (An Account can be writen different lines. ) ------------------------------------------|----------------------------- HP_Level..HP_No..HP_AccountName...........|....MFD_AccountNo..MFD_Total 1.........1......Main1Account.............|....1200254........10,000 2.........10.....Main1SubAccount1.........|....1002865........15,000 3.........100....SafeBoxes................|....1009431........20,000 2.........12.....Main1SubAccount2.........|....2005454.........0,500 3.........120....Customers................|....2000024.........5,000 1.........2......Main2Account.............|....1205471........35,000 2.........20.....Main2SubAccount1.........|....1205471.........0,600 3.........200....Renders..................|....1205471.........0,400 ...............................................2006300........48,500 All I want is a result Set Like HP_No..HP_AccountName.........TOTAL 1......Main1Account...........81,000 10.......Main1SubAccount1.....35,000 100........SafeBoxes..........35,000 20.......Main1SubAccount2.....46,000 120........Customers..........46,000 2......Main2Account...........54,000 20.......Main2SubAccount1.....54,000 200........Renders............54,000 I worte something like that but i couldnt do axactly what i want Select SPACE(HP_Level*3)+ HP_AccountName, (Select SUM(MFD_Total) FROM T_VALUES WHERE MFD_AccountNo LIKE HP_No + '%') FROM T_ACCOUNTS WHERE HP_Level<=1 order by HP_No -- modified at 7:22 Thursday 16th February, 2006
-
There is two tables HP_AccountName and T_Values. HP_AccountName includes all main accounts,T_Values includes all operations about accounts. Sample Tables r fallowing. HP_AccountName............................|T_Values (An Account can be writen different lines. ) ------------------------------------------|----------------------------- HP_Level..HP_No..HP_AccountName...........|....MFD_AccountNo..MFD_Total 1.........1......Main1Account.............|....1200254........10,000 2.........10.....Main1SubAccount1.........|....1002865........15,000 3.........100....SafeBoxes................|....1009431........20,000 2.........12.....Main1SubAccount2.........|....2005454.........0,500 3.........120....Customers................|....2000024.........5,000 1.........2......Main2Account.............|....1205471........35,000 2.........20.....Main2SubAccount1.........|....1205471.........0,600 3.........200....Renders..................|....1205471.........0,400 ...............................................2006300........48,500 All I want is a result Set Like HP_No..HP_AccountName.........TOTAL 1......Main1Account...........81,000 10.......Main1SubAccount1.....35,000 100........SafeBoxes..........35,000 20.......Main1SubAccount2.....46,000 120........Customers..........46,000 2......Main2Account...........54,000 20.......Main2SubAccount1.....54,000 200........Renders............54,000 I worte something like that but i couldnt do axactly what i want Select SPACE(HP_Level*3)+ HP_AccountName, (Select SUM(MFD_Total) FROM T_VALUES WHERE MFD_AccountNo LIKE HP_No + '%') FROM T_ACCOUNTS WHERE HP_Level<=1 order by HP_No -- modified at 7:22 Thursday 16th February, 2006
You might want to put your table examples inside <pre></pre> blocks. That way it will be readable. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell