Nested selects ?? (Acounting SQL!!! )

# Thread: Nested selects ?? (Acounting SQL!!! )

1. Conn Guest

## Nested selects ?? (Acounting SQL!!! )

Hi, can i do a nested SQL statemente over two tables?<BR><BR>Table 1<BR> Account : Title<BR> 1 Capital<BR> 100 Capital Social<BR> 10000 Capital Social Privado<BR> 10000000 Capital<BR><BR>Table 2<BR> Account Date Amount<BR> 10000000 1/1/99 50.000<BR><BR>What i need to do is SUM the amounts from Table2 grouping by Amount such that I have a intermediary table of<BR> <BR> Account Sum<BR> 10000000 150.000<BR> 20000000 250.000<BR><BR>Then i need to use Table 1 to divide the 8 digit accounts into groups of 1,3 and 5 so that my results are like<BR><BR> Account Sum<BR> 1 250.000<BR> 100 100.000<BR> 101 50.000<BR> 102 100.000<BR><BR>Please help!!

2. Senior Member
Join Date
Dec 1969
Posts
274

## RE: Nested selects ?? (Acounting SQL!!! )

I don&#039t understand the last part (and possibly the first part), could you explain it a little more? What is the relationship of table 1 to table2? What are you trying to do in the last part? Will table2 have multiple entries per table 1? Why do you provide sample data but then change the sample data? I am confused by this.<BR><BR>to get the sums:<BR><BR>select t2.account, t2.sum(amount) from table_2 t2<BR>group by t2.account

3. Conn Guest

## RE: Nested selects ?? (Acounting SQL!!! )

An accounting program has a &#039plan&#039 table which lists accounts<BR>in a tree structure called groups, in this case i use groups of 1 digit,3,5 and 8 for example<BR><BR>1 Capital (is group 1)<BR>100 Capital Social (is group 3)<BR>10001 Capital private (is group 5)<BR>10001001 Capital ShareHolder 1 (is group 8)<BR>10001002 Capital ShareHolder 2 (is group 8)<BR>10002 Capital Public (is group 5)<BR>10002001 Capital Public ShareHolder 1<BR>10002002 Capital Public ShareHolder 2<BR><BR>In the &#039Accounting&#039 table in which one registers the movement<BR>between different accounts one only uses group 8 accounts such<BR>that an example of the &#039Accounting&#039 table would be<BR><BR>10001001 01/01/1999 250.000 Investment cap. by priv.Shareholder 1<BR>10001002 01/01/1999 250.000 Investment cap. by priv.Shareholder 2<BR><BR>such that the relation abount the &#039Plan&#039 table and the &#039Accounting&#039 table are the account codes, BUT, only account codes of group 8 can appear in the &#039accounting&#039 table.<BR><BR>A join would take the form<BR><BR> Plan INNER JOIN Accounting ON plan.account=accounting.account<BR><BR>My problem stems from the fact that the accounting table ONLY has group 8 accounts, whereas I need to be able to provide a relation of sums for ALL the groups at the same time where each branch in the &#039Plan&#039 tree would be the sum of its children.<BR><BR>Thanks for your time, help and interest...<BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
274

## RE: Nested selects ?? (Acounting SQL!!! )

If restructuring the tables is not out of the question, this might be the best solution. <BR><BR>Otherwise, you could try using the LEFT function.<BR><BR>Something like:<BR><BR>Select Sum(t Amount) from Plan_Table p inner join Accounting_Table a on p.ID = left(a.id, len(p.id))<BR>group by a.ID<BR><BR>or something like that (no idea if that is even close to correct syntax).

#### Posting Permissions

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