SQL to search through a list of ID's/numbers on a temporary table

I have two tables, table Customer and Table CustomerAccount

There are customers in our database who are agents.

An Agent can be a super Agent with many agents underneath him. Also the tricky bit is trying to find those Agents (under a Super Agent) who have agents underneath them too!

- so we have IDSuperAgent number (not all agents are super agents and have a null value for this field) and IDAgent number (All agents must have IDAgent number).  All accounts have a IDAccount number regardless of level.

IDSuperAgent          IDAgente               IDAccount            Balance    IDTypeOfAccount
 29500                     29500                    29500                  5000                    2
29500                      50                          50                        500                      2
29500                      49                          49                        1500                    2      
49                            48                          48                        2000                    2      
48                            47                          47                        500                      2

There is no flag to separate each level in the table, so we can only find the agents from the normal customers by using the IDTypeOfAccount field (which will be 2  =  agents) .

I have attached the SQL which is trying to read from a temporary table where I am trying to add the balance of the Agents under the SuperAgent and those agents which have agents underneath themselves.

Please can you help in identifying perhaps a better way to query the information or help fix my attempted query to get a list of id's i put in a temporary table in order to capture the correct id's

I think a derived table might solve it but i am not very used to creating this type of query.

You help is most appreciated.

Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

drop table #tmpCustomer
drop table #tmpData
 
-- get Agents who belong to super agent 29500
SELECT    tbl_Customer.IDAgent, tbl_CustomerAccount.Balance
into #tmpCustomer
FROM         tbl_Customer WITH (nolock) INNER JOIN
                      tbl_CustomerAccount WITH (nolock) ON tbl_Customer.IDUtente = tbl_CustomerAccount.IDUtente
                     
WHERE       tbl_Customer.IDSuperAgent = '29500' 
and tbl_Customer.IDTypeOfAccount = 2
group by tbl_Customer.IDAgent, tbl_CustomerAccount.Balance
order by tbl_Customer.IDAgent
 
 
-- get all agents under the above agents (who sit under super agent 29500) captured in the query above
SELECT  tbl_Customer.IDAgent, tbl_Customer.Name, tbl_Customer.Surname, tbl_CustomerAccount.Balance
into #tmpData
FROM         tbl_Customer WITH (nolock) INNER JOIN
                      tbl_CustomerAccount WITH (nolock) ON tbl_Customer.IDUtente = tbl_CustomerAccount.IDUtente
                      INNER JOIN #tmpCustomer tmp  on tmp.IDAgent = u.IDSuperAgent
                     
WHERE       (tbl_Customer.IDAgent in u.IDSuperAgent)  --<---- where I need help not sure how to query against the list in tmpcustomer
and tbl_Customer.IDTypeOfAccount = 2 and tbl_Customer.IDSuperAgent is not null
group by  tbl_Customer.IDAgent, tbl_Customer.Name, tbl_Customer.Surname, tbl_CustomerAccount.Balance
 
 
-- get all agents data and under agents combined
 
Select distinct tmp.IDAgent, tmp.Name, tmp.Surname, sum(tmp.Balance + cu.Balance)
from tbl_Customer as u with (nolock) inner join
tbl_CustomerAccount as cu with (nolock) ON tbl_Customer.IDAgent = tbl_CustomerAccount.IDUtente
                     
WHERE       tbl_Customer.IDSuperAgent = '29500' 
and tbl_Customer.IDTypeOfAccount = 2 
group by tmp.IDAgent, tmp.Name, tmp.Surname