CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2019
papa dp
papa dp

Reputation: 65

Join with union returns me wrong result

I need to use union all twice. And then to join them with the same table. First union will contain a where statement and the second it will not. Problem is that when I use the second table my result is changing.

select sum(x.quantity*x.Price)
from CustomerTrans t1 
inner join (
    select *
    from InventoryTrans
    union all
    select *
    from InventoryTransTemp
) x on t1.TrnDocumentID = x.TrnDocumentID
group by t1.TrnDocumentID

Here is the output from this result

First Result enter image description here

Then I am adding the second union with the where statement inside

select sum(x.quantity*x.Price), sum(x2.quantity*x2.Price)
from CustomerTrans t1 
left join (
    select *
    from InventoryTrans
    union all
    select *
    from InventoryTransTemp
) x on t1.TrnDocumentID = x.TrnDocumentID
left join (
    select *
    from InventoryTrans
    where printed = 2 or InvoicePaymentID = 2 
    union all
    select *
    from InventoryTransTemp
    where printed = 2 or InvoicePaymentID = 2
) x2 on t1.TrnDocumentID = x2.TrnDocumentID 
group by t1.TrnDocumentID

Here is the second result

Second Result enter image description here

Second result it should be 3.80 and not 7.60

It look like it multiples my price *2 instead *1.

Upvotes: 0

Views: 199

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

What happens here is that you join rows you don't want to join. Let's say your first subquery returns

+----------+-------+
| quantity | price |
+----------+-------+
|       10 |   100 |
|       10 |   200 |
+----------+-------+

for a particular document ID. And your second subquery returns only

+----------+-------+
| quantity | price |
+----------+-------+
|       10 |   200 |
+----------+-------+

The joined result is:

+------------+---------+-------------+----------+
| x.quantity | x.price | x2.quantity | x2.price |
+------------+---------+-------------+----------+
|         10 |     100 |          10 |      200 |
|         10 |     200 |          10 |      200 |
+------------+---------+-------------+----------+

And the aggregations results thereafter are:

+----------+-----------+
| x_result | x2_result |
+----------+-----------+
|     3000 |      4000 |
+----------+-----------+

instead of

+----------+-----------+
| x_result | x2_result |
+----------+-----------+
|     3000 |      2000 |
+----------+-----------+

Instead of joining single rows, you want to join aggregation results (the totals per document):

select 
  ct.*,
  coalesce(u1.total, 0) as u1_total,
  coalesce(u2.total, 0) as u2_total
from customertrans ct
left join 
(
  select trndocumentid, sum(quantity * price) as total
  from
  (
    select * from inventorytrans
    union all
    select * from inventorytranstemp
  ) union1
  group by trndocumentid
) u1 on u1.trndocumentid = ct.trndocumentid
left join 
(
  select trndocumentid, sum(quantity * price) as total
  from
  (
    select * from inventorytrans where printed = 2 or invoicepaymentid = 2 
    union all
    select * from inventorytranstemp where printed = 2 or invoicepaymentid = 2
  ) union2
  group by trndocumentid
) u2 on u2.trndocumentid = ct.trndocumentid 
group by ct.trndocumentid
order by ct.trndocumentid;

Upvotes: 1

Related Questions