Click here to Skip to main content
15,894,405 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 3 table: tbl1, tbl2, tbl3 . Each table has quantity and ItemID column. I need to search by ItemId in those table and get the total quantity in store procedure.


Actually I need (tbl1+tbl2-tbl3)=totalqty with ItemId
Plz help
Posted
Updated 9-Jan-15 21:06pm
v2

1 solution

Not sure with your question,Hope this may help you.

If your column datatype are similar you can use 'union all' in sql.
If you need list of Quantity column,then try like this,
SQL
declare @ItemId int=1;
with cte as (select Quantity,ItemId from tbl1 a union all 
select Quantity,ItemId  from tbl2 b union all 
select Quantity,ItemId  from tbl3 c) select Quantity from cte where ItemId=@ItemId

//It will return list of quantity column

If you need Sum of quantity,Do like this,
SQL
declare @ItemId int=1;
with cte as (select Quantity,ItemId from tbl1 a union all 
select Quantity,ItemId  from tbl2 b union all 
select Quantity,ItemId  from tbl3 c) select SUM(Quantity) as TotalQuantity from cte where ItemId=@ItemId
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900