?
Solved

sql query to join 3 tables

Posted on 2012-09-16
24
Medium Priority
?
608 Views
Last Modified: 2012-10-12
I used the below query to get the data from the below 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,0 as 'RequestAmount'
from A,B
where  B.companyname=A.companyname
group by A.companyname

union

select 0 as 'tocompanytotal', 0 as 'fromcompanytotal',A.companyname,sum(C.TotalAmount) as RequestAmount
from A,C
where A.companyname=c.companyname
group by A.companyname

Result is:

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


The results are from Tables A, B and C
Where A has the the data for the above as:

Table A
CBRNO
B1
D5


Table B
TOcompanyTotal      FromCompanyTotal   companyname             
7000000.00               7000000.00          B1               
537690.00               537690.00          D5      

C Table has the data :
companyname       RequestAmont    
D5               537690.00

But I want the data as below
TOcompanyTotal      FromCompanyTotal   companyname             RequestAmount
7000000.00               7000000.00          B1                          0.00
537690.00               537690.00          D5                          537690.00

when I used the below query it won't give me any data as there is no matching itsme when in table B but not C

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,C.totalamount as 'RequestAmount'
from A,B,C
where  B.companyname=A.companyname and A.companyname=C.CompanyName
group by A.companyname


Kindly help?
0
Comment
Question by:welcome 123
  • 13
  • 8
  • 3
24 Comments
 

Expert Comment

by:Jamieo1
ID: 38403694
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
0
 

Author Comment

by:welcome 123
ID: 38403857
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?
0
 

Author Comment

by:welcome 123
ID: 38403881
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:Jamieo1
ID: 38403983
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?
0
 

Author Comment

by:welcome 123
ID: 38404171
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.
0
 

Expert Comment

by:Jamieo1
ID: 38404217
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
0
 

Author Comment

by:welcome 123
ID: 38404246
*will try this first thing tomorrow morning and let you know.
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38405811
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"
0
 

Author Comment

by:welcome 123
ID: 38406288
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.
0
 

Author Comment

by:welcome 123
ID: 38406289
I still hae to try the temp table thing
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38407227
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
0
 

Author Comment

by:welcome 123
ID: 38433340
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
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38433419
Does "CBRequest"  table have unique and complete list of "agency" (CBRNO) names (looking for a primary key)?
0
 

Author Comment

by:welcome 123
ID: 38433757
CBRequest table defenetly has unique CBNO's.
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38434049
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...
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38434084
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
0
 

Author Comment

by:welcome 123
ID: 38434710
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
0
 

Author Comment

by:welcome 123
ID: 38434736
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
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38436529
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
0
 

Author Comment

by:welcome 123
ID: 38438786
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
0
 
LVL 2

Accepted Solution

by:
gleb_l earned 2000 total points
ID: 38441580
Just need to add another grouping:

select g."CBRNO", g."TOAgencyTotal", g."FromAgencyTotal",sum(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"
group by
g."CBRNO", g."TOAgencyTotal", g."FromAgencyTotal",g."Directorapproval"
order by 1
0
 
LVL 2

Expert Comment

by:gleb_l
ID: 38463079
Does this work for you now?
0
 

Author Comment

by:welcome 123
ID: 38490153
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.
0
 

Author Comment

by:welcome 123
ID: 38490186
works superb. Thanks for all the great help and excellent solution and aplolozise for the delay once again.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question