CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2516394
user2516394

Reputation: 81

Select query with join multiple tables in SQL Server

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

Upvotes: 2

Views: 8170

Answers (2)

DaVinci007
DaVinci007

Reputation: 728

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

Upvotes: -1

Vasanth
Vasanth

Reputation: 1710

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

Upvotes: 3

Related Questions