Select query with join multiple tables in SQL Server

Question

I've 3 tables, A, B and C, with columns

UserId, CompanyId, Quantity, Rate

I want to do query which return results like this query below

SELECT 
    (A.Quantity + B.Quantity + C.Quantity) AS TotalQuantity, 
    SUM(A.Quantity * A.Rate) + SUM(C.Quantity * C.Rate) AS TotalAmount, 
    TotalQuantity/TotalAmount AS Result 
FROM 
    A, B, C  
WHERE
    (A.UserId = 1 AND A.CompanyId = 1) 
    AND 
    (A.UserId = B.UserId AND A.UserId = C.UserId AND A.CompanyId = B.CompanyId 
     AND A.CompanyId = C.CpmpanyId)

I've tried to run this query but its not working the way it suppose to be. I'm missing some thing here. Kindly help me.

Table A 

UID    CID    Quantity  Rate
1       1      90        60
1       1      100        9


Table B

UID    CID    Quantity
1       1       100
1       1       50

Table C

UID    CID    Quantity  Rate
1       1       5        5
1       1       5        5

What is it doing? Is there an error? What is that error? Is the output not what you expect? What are the specific inputs and outputs of this statement, and what did you expect? — jmarkmurphy
– jmarkmurphy, Commented Jun 26, 2013 at 20:38
its adding every thing twice! e.g if i have A.Quantity=100, its adding 200+B.Quantity — user2516394
– user2516394, Commented Jun 26, 2013 at 20:46
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (more than 20 years ago) — marc_s
– marc_s, Commented Jun 26, 2013 at 20:47

Vasanth · Accepted Answer · 2013-06-26 21:42:30Z

3

Changing the query based on the edited question:

select sum(Quantity) TotalQuantity, sum(Quantity * Rate) TotalAmount, sum(Quantity) / sum(Quantity * Rate * 1.0) Result
from
(
   select UID, CID, Quantity, Rate
   from A
   union all
   select UID, CID, Quantity, 1 Rate
   from B
   union all
   select UID, CID, Quantity, Rate
   from C
) t
where
   t.UID = 1 
   and t.CID = 1

Also here is a working example

edited Jun 26, 2013 at 21:42

answered Jun 26, 2013 at 20:45

Vasanth

1,7101 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lamak Over a year ago

+1, good to see that you changed the query to use proper explicit joins

user2516394 Over a year ago

i've total quantity=350, but why this query return 1400 in Total Quantity, same goes for Total Amount

Vasanth Over a year ago

Need to see the values that's available in A,B & C tables for UserId = 1 and CompanyId=1 to answer your comment.

user2516394 Over a year ago

I've only UserId, CompanyId & Quantity Field in Table B , is this the problem

HLGEM Over a year ago

When you are doing a division or an addition of numbers you need to handle nulls if there is a possibility that one of teh vlues coudl be null. This is not important if your table structure for these fields is not null, but should be handled if nulls are allowed.

|

DaVinci007 · Accepted Answer · 2013-06-27 00:42:18Z

-1

The problem with the data is that the joins duplicate the rows. A.UID=B.UID AND A.CID=B.CID links each row twice.

Try using unions as following

SELECT SUM(t.TotalQuantity), SUM(t.TotalAmount), SUM(t.TotalQuantity)/SUM(t.TotalAmount) FROM (
    SELECT 
        CID, UID, SUM(Quantity) AS TotalQuantity, SUM(Quantity*Rate) AS TotalAmount
    FROM A 
    GROUP BY CID, UID
    UNION ALL
    SELECT 
        CID, UID, SUM(Quantity), 0
    FROM B 
    GROUP BY CID, UID
    UNION ALL
    SELECT 
        CID, UID, SUM(Quantity), SUM(Quantity*Rate)
    FROM C 
    GROUP BY CID, UID
) t
WHERE t.UID=1 AND t.CID=1

To have the data grouped by Company as asked in the comments:

SELECT t.CID, SUM(t.TotalQuantity), SUM(t.TotalAmount), SUM(t.TotalQuantity)/SUM(t.TotalAmount) FROM (
    SELECT 
        CID, UID, SUM(Quantity) AS TotalQuantity, SUM(Quantity*Rate) AS TotalAmount
    FROM A 
    GROUP BY CID, UID
    UNION ALL
    SELECT 
        CID, UID, SUM(Quantity), 0
    FROM B 
    GROUP BY CID, UID
    UNION ALL
    SELECT 
        CID, UID, SUM(Quantity), SUM(Quantity*Rate)
    FROM C 
    GROUP BY CID, UID
) t
WHERE t.UID=1
GROUP BY t.CID

edited Jun 27, 2013 at 0:42

answered Jun 26, 2013 at 20:43

DaVinci007

7185 silver badges11 bronze badges

7 Comments

user2516394 Over a year ago

same problem, its adding every thing twice! e.g if i have A.Quantity=100, its adding 200+B.Quantity

marc_s Over a year ago

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (more than 20 years ago)

DaVinci007 Over a year ago

True, I wanted to keep the format of the original post for clarity. Will use proper syntax next time.

DaVinci007 Over a year ago

@user2516394 Are you sure the tables are joined correctly? If you are missing a key field in the join, it might double the rows.

user2516394 Over a year ago

I've only UserId, CompanyId & Quantity Field in Table B

|

Collectives™ on Stack Overflow

Select query with join multiple tables in SQL Server

2 Answers 2

7 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related