February 22, 2011 at 10:08 am
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.
February 22, 2011 at 10:35 am
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
February 22, 2011 at 10:50 am
Thanxs I Thats a good idea. I'm working on that right know, I'll let you know soon when I finish it.
February 23, 2011 at 7:59 am
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.
February 23, 2011 at 10:12 am
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
February 23, 2011 at 10:47 am
Thanks that worked for me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply