sql query to join 3 tables

select SUM(CASE WHEN B.TOCompany=1 and transfer.fromCompany=0 THEN B.Amount ELSE 0 END) as 'TOcompanyTotal'
     , SUM(CASE WHEN B.Fromagency=1 and transfer.toagency=0 THEN transfer.Amount ELSE 0 END) as 'FromCompanyTotal',
     A.companyname,
     ISNULL(C.totalamount,0) as 'RequestAmount'
FROM A INNER JOIN B ON B.companyname=A.companyname
LEFT JOIN  C ON  A.companyname=C.CompanyName
group by A.companyname

TOcompanyTotal      FromCompanyTotal      companyname      RequestAmount
7000000.00      7000000.00      B1                         0.00
537690.00      537690.00      D5                      1075380.00

I got the above results using the query which is very close except for the amount doubled i think in the request amount for d5company. what should i change in the query to get the right amount in table c for request amount?

sorry I was using sum(c.totalamount) and replaced it with your query and added the c.totalamount in the group by which gave me the results I want. But if I had 2 rows of amount values in C table for the same company then will the ame query work?

I used:

select SUM(CASE WHEN B.TOCompany=1 and transfer.fromCompany=0 THEN B.Amount ELSE 0 END) as 'TOcompanyTotal'
     , SUM(CASE WHEN B.Fromagency=1 and transfer.toagency=0 THEN transfer.Amount ELSE 0 END) as 'FromCompanyTotal',
     A.companyname,
     ISNULL(C.totalamount,0) as 'RequestAmount'
FROM A INNER JOIN B ON B.companyname=A.companyname
LEFT JOIN  C ON  A.companyname=C.CompanyName
group by A.companyname ,C.totalamount

When I read your first comment, I thought I should have included sum(ISNULL(c.totalamount,0))  as 'RequestAmount'

Have you tried removing c.totalamount from the group by statement and substituting the above with sum(ISNULL(c.totalamount,0))  as 'RequestAmount' ?

Maybe changing the join from
LEFT JOIN  C ON  A.companyname=C.CompanyName
to
LEFT JOIN  C ON  B.companyname=C.CompanyName
will make a difference?

well when I substitute the sum(ISNULL(c.totalamount,0))  as 'RequestAmount'  in the group by clause it gives me an error saying: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

and by usning the query with sum gives me the one row  result which is doubled amount:

select SUM(CASE WHEN B.TOCompany=1 and transfer.fromCompany=0 THEN B.Amount ELSE 0 END) as 'TOcompanyTotal'
     , SUM(CASE WHEN B.Fromagency=1 and transfer.toagency=0 THEN transfer.Amount ELSE 0 END) as 'FromCompanyTotal',
     A.companyname,
     sum(IS(NULL(C.totalamount,0)) as 'RequestAmount'
FROM A INNER JOIN B ON B.companyname=A.companyname
LEFT JOIN  C ON  B.companyname=C.CompanyName
group by A.companyname

There is no difference on changing the left join to a.companyname or B.companyname and even using innerjoin etc.

Interesting - well might be best to do it in two stages.  Create a temp table or use a table variable -  (fill in your variable types, lengths)

CREATE TABLE
#Final
(ToCompanyTotal decimal(?,2)
FromCompanyTotal decimal(?,2)
CompanyName varchar(?)
RequestAmount decimal(?,2))

INSERT INTO #final (ToCompanyTotal,FromCompanyTotal,CompanyName)
select SUM(CASE WHEN B.TOCompany=1 and transfer.fromCompany=0 THEN B.Amount ELSE 0 END) as 'TOcompanyTotal'
     , SUM(CASE WHEN B.Fromagency=1 and transfer.toagency=0 THEN transfer.Amount ELSE 0 END) as 'FromCompanyTotal',
     A.companyname
FROM A INNER JOIN B ON B.companyname=A.companyname
group by A.companyname

UPDATE f
SET RequestAmount = SUM(totalamount)
FROM  #final f  join c on c.companyname = f.companyname
Group by f.Companyname

SELECT ToCompanyTotal,FromCompanyTotal,CompanyName,ISNULL(RequestAmount,0)
FROM
#Final

*will try this first thing tomorrow morning and let you know.

Assuming that column CBRNO of A has UNIQUE values (company names):

select sum(nvl(B."TOcompanyTotal",0)) as "TOcompanyTotal", sum(nvl(B."FromCompanyTotal",0)) as "FromCompanyTotal", t."companyname",t."RequestAmount"
from
(
select  A."CBRNO" as "companyname", sum(nvl(C."RequestAmount",0)) as "RequestAmount"
from A left join C on A."CBRNO"=C."companyname"
group by A."CBRNO"
)
t left join B on t."companyname"=B."companyname"
group by t."companyname", t."RequestAmount"

Actually i was replacing the cbrno with companyname when asking the query and by mistake the cbrno was still there , it should be the campanyname instead and yes the companyname is unique in the A table where as repeated in B and C tables
 and i need to get one row of data for each unique company name with the sum of total amount values in B and C tables for that companynames. sometimes the companyname will have data in B table and not in C and viceversa. When i used the query above it gives me one row of data ith amount doubled:

select SUM(CASE WHEN B.TOCompany=1 and transfer.fromCompany=0 THEN B.Amount ELSE 0 END) as 'TOcompanyTotal'
     , SUM(CASE WHEN B.Fromagency=1 and transfer.toagency=0 THEN transfer.Amount ELSE 0 END) as 'FromCompanyTotal',
     A.companyname,
     sum(IS(NULL(C.totalamount,0)) as 'RequestAmount'
FROM A INNER JOIN B ON B.companyname=A.companyname
LEFT JOIN  C ON  B.companyname=C.CompanyName
group by A.companyname


I am using sqlserver 2005 database and the front end is vb.net

when i tried your query above i get nvl is not recognized.

I still hae to try the temp table thing

Sorry I used Oracle function NVL there.
replace it with ISNULL using the same arguments.

select sum(ISNULL(B."TOcompanyTotal",0)) as "TOcompanyTotal", sum(ISNULL(B."FromCompanyTotal",0)) as "FromCompanyTotal", t."companyname",t."RequestAmount"
from
(
select  A."CBRNO" as "companyname", sum(ISNULL(C."RequestAmount",0)) as "RequestAmount"
from A left join C on A."CBRNO"=C."companyname"
group by A."CBRNO"
)
t left join B on t."companyname"=B."companyname"
group by t."companyname", t."RequestAmount

well let me give the original tale names as I am totally getting confused here please help and sorry for the repetition and delay:

Table CBRequest
CBRNO  OBMDirectorapproval
B1            0
C1            0
D1           1

Table Transfer
ToAgency                   FromAgency                  CBRNO         TransferAmt              
 1                                     0                                 B1              300.00
 0                                     1                                 B1               200.00
 0                                     1                                 B1               100.00
 1                                     0                                 c1                400.00
 0                                     1                                 c1                100.00
 0                                     1                                 c1                300.00
 
Table Funding
CBRno     Total amount    
D1               500.00
B1               300.00


But the user wants the data as follows:
CBRNO        TOAgencyTotal      FromAgencyTotal   Totalamount       Directorapproval
B1                 100                       300                        600                    NO
C1                  800                      400                                                  NO
D1                                                                           500                    Yes
                           

And the query I am using is:
select SUM(CASE WHEN transfer.TOagency=1 and transfer.fromagency=0 THEN transfer.transferAmt ELSE 0 END) as 'TOagencyTotal'
     , SUM(CASE WHEN transfer.Fromagency=1 and transfer.toagency=0 THEN transfer.transferamt ELSE 0 END) as 'FromagencyTotal',
     cbrequest.cbrno, CASE WHEN CBRequest.OBMDirectorapproval = 1 THEN 'YES' ELSE 'NO' END as 'SignOff?'
     ,isnull(funding.totalamount,0) as 'totalamount'
 FROM cbrequest INNER JOIN transfer ON transfer.cbrno=cbrequest.cbrno
left JOIN  funding ON  transfer.cbrno=funding.cbrno
where cbrequest.cbrno in('B1','C1','D1')
group by cbrequest.cbrno ,funding.totalamount,cbrequest.OBMDirectorApproval

The results I got:
TOagencyTotal      FromagencyTotal      cbrno      SignOff?      totalamount
300.00              300.00              B1                        NO             300.00
400.00              400.00              C1                       NO               0.00

Does "CBRequest"  table have unique and complete list of "agency" (CBRNO) names (looking for a primary key)?

CBRequest table defenetly has unique CBNO's.

SQL aside...

I don't quite understand how the user comes up with those numbers. What is the general logic and math for calculating e.g "TOAgencyTotal" for B1?

I assume that if the respective TransferAmt for B1 is flagged with ToAgency="1" in Transfer table it sums up to 300.00 for "TOAgencyTotal" for B1. That's what yoou have but the user "wants" 100.00... How come?

Totalamount values in the user tabel do not seem to compute well either...

in any case... Here's another version. I really struggle with your inconsistent capitalization - so you'll probaly struggle with mine :-)

select c."CBRNO", sum("TransferAmt" * "ToAgency") as "TOAgencyTotal", sum("TransferAmt" * "FromAgency") as "FromAgencyTotal",
sum(f."TOTAL_AMOUNT") as "Totalamount",
CASE WHEN c."OBMDirectorapproval"=1 THEN 'YES' ELSE 'NO' END as "Directorapproval"
from "CBRequest" c left join "Transfer" t on c."CBRNO"=t."CBRNO" left join FUNDING f on c."CBRNO"=f."CBRNO"
group by c."CBRNO", c."OBMDirectorapproval"
order by 1

before evn I try your query I apolozise for my typos plesae excuse me , you are right the user wants the below data:

CBRNO        TOAgencyTotal      FromAgencyTotal   Totalamount       Directorapproval
B1                  300                       300                        300                    NO
C1                  400                       400                            0                     NO
D1                     0                          0                          500                    Yes

Yes I used the query you mentioned below adding a where caluse so I can compare results acurately:

select c.CBRNO, sum(TransferAmt * ToAgency) as TOAgencyTotal,
sum(TransferAmt * FromAgency) as 'FromAgencyTotal',
sum(f.TOTALAMOUNT) as 'Totalamount',
CASE WHEN c.OBMDirectorapproval=1 THEN 'YES' ELSE 'NO' END as 'Directorapproval'
from CBRequest c left join Transfer t on c.CBRNO=t.CBRNO left join FUNDING f on c.CBRNO=f.CBRNO
where c.cbrno in('B1','C1','D1')
group by c.CBRNO, c.OBMDirectorapproval
order by 1


and I got the below result:

CBRNO      TOAgencyTotal      FromAgencyTotal      Totalamount      Directorapproval
B1      300.00                    300.00                        900.00                           NO
C1      400.00                    400.00                           NULL                            NO
D1      NULL                     NULL                         500.00                            YES

But I am expecting the funding amount fo b1 to be 300


CBRNO      TOAgencyTotal      FromAgencyTotal      Totalamount      Directorapproval
B1      300.00                    300.00                        300.00                           NO
C1      400.00                    400.00                           NULL                            NO
D1      NULL                     NULL                         500.00                            YES

Got it! Sorry now my bad... Slight adjustment:

select g."CBRNO", g."TOAgencyTotal", g."FromAgencyTotal",f."TOTAL_AMOUNT" as "Totalamount",g."Directorapproval" from
(
select c."CBRNO", sum("TransferAmt" * "ToAgency") as "TOAgencyTotal", sum("TransferAmt" * "FromAgency") as "FromAgencyTotal",
CASE WHEN c."OBMDirectorapproval"=1 THEN 'YES' ELSE 'NO' END as "Directorapproval"
from "CBRequest" c left join "Transfer" t on c."CBRNO"=t."CBRNO"
where c.cbrno in('B1','C1','D1')
group by c."CBRNO", c."OBMDirectorapproval"
) g left join FUNDING f on g."CBRNO"=f."CBRNO"
order by 1

I got the result I wanted but there is a problem:

If there are multiple entries for a cbrno in funding table then the result is different

Example:
I add one more row for cbrno b1 in funding table which is:

insert into funding(TotalAmount,CBRNo) values (100,'B1')

Now fuding table looks like:
cbrno      totalamount
B1      300.00
B1      100.00
D1      500.00

The result is:
CBRNO      TOAgencyTotal      FromAgencyTotal      Totalamount      Directorapproval
B1      300.00                         300.00                      300.00                                      NO
B1      300.00                         300.00                      100.00                                     NO
C1      400.00                         400.00                       NULL                                     NO
D1      NULL                         NULL                       500.00                                    YES

But the user wants:

CBRNO      TOAgencyTotal      FromAgencyTotal      Totalamount      Directorapproval
B1      300.00                         300.00                      400.00                                      NO
C1      400.00                         400.00                       NULL                                     NO
D1      NULL                         NULL                       500.00                                    YES

Does this work for you now?

I am sorry for the delay as I got sick severely and was out of office for a while and got busy with some other things later. I am back now will try the above query and let you know ASAP.

works superb. Thanks for all the great help and excellent solution and aplolozise for the delay once again.