Difference in tables columns

Question

Post reply

Difference in tables columns

camiloaguilar1

Say Hey Kid

Points: 667
More actions
February 22, 2011 at 10:08 am

#231570

Table 1
OrderID | ProductID | Quantity | Price | Total
----------------------------------------------
1 x 3 30 90
2 y 5 20 100
3 z 1 40 40
Table 2
OrderID | ProductID | Quantity | Price | Total
-----------------------------------------------
1 x 1 30 30
2 y 2 20 20
Result
OrderID | ProductID | Quantity | Price | Total
------------------------------------------------
1 x 2 30 60
2 y 3 20 60
3 z 1 40 40
As you see I have to tables. And I want to returns the diference in quantity from each table.
If a row in table1 is not on table2, show it always.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Hi,

Is this what you needed.

declare @Tb1 table (OrderID int, ProductID varchar(2), Quantity int, Price int ,Total int )

declare @Tb2 table (OrderID int, ProductID varchar(2), Quantity int, Price int ,Total int )

Insert @Tb1

Select 1,'x',3,30,90

union all Select 2 ,'y',5,20,100

union all Select 3,'z',1,40,40

Insert @Tb2

Select 1,'x',1,30, 30

union all Select 2,'y',2,20, 20

Select * from @Tb1

Select * from @Tb2

Select a.OrderID,a.Productid ,

Isnull((Select a.Quantity-b.Quantity from @Tb2 as b where a.orderid=b.OrderID),a.Quantity) as Quantity,

a.Price ,

Isnull((Select a.Quantity-b.Quantity from @Tb2 as b where a.orderid=b.OrderID),a.Quantity)*Price as Total

from @Tb1 as a

There might be some other easy ways will be there

Thanks
Parthi

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 2

Thanxs I Thats a good idea. I'm working on that right know, I'll let you know soon when I finish it.

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 3

It definitely that worked out for me.

I just want to ask something else.

When I make something like the query above..

Select a.Id, (Select Sum(b.quantity) from TableB b from b.Id=a.Id) AS Total from TableA a where Total>100

I will like to use that column Total in a where clause, but sql keeps saying me that column Total is not valid

I have this other scenario too

Select a.Id, (Select Sum(b.quantity) from TableB b from b.Id=a.Id) AS Total, (Select Sum(c.quantity) from TableC c from c.Id=a.Id) AS Total2, (Total*Total2) as GranTotal from TableA a where Total>100

I can't use the columns computed with the subquery, so I can multiply and get another column.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

SELECT a.Id, b.total

From TableA a

LEFT OUTER JOIN (SELECT b.Id, SUM(b.quantity) AS total FROM TableB b GROUP BY b.Id) b ON a.ID = b.Id

where b.total>100

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

camiloaguilar1 Say Hey Kid Points: 667 More actions · Answer 5

camiloaguilar1

Say Hey Kid

Points: 667

February 23, 2011 at 10:47 am

#1290538

Thanks that worked for me!