SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combine two tables in one recordset?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 10/01/2013 :  22:48:47  Show Profile  Reply with Quote
God day!

I have a little problem combining in 2 tables in 1 records the result of return/exchange query I have created..

Currently I have this stored proc below.

ALTER PROCEDURE sp_Get_Return_Exchange
@orno as varchar(10)
AS 
SET NOCOUNT ON;

select p.poref as pofrefReturn,p.productcode as  pdocodeReturn,p.itemname as itemnameReturn, p.qty as QtyReturn,p.price as priceReturn ,p.net as NetReturn
from tbl_PO_detail p
where p.poref=@orno and p.memo='return/exchange'

UNION ALL

select sd.salesid as salesidExchange,sd.productcode as pdocodeExchange,sd.productdesc as proddescExchange,sd.qty as QtyExchange,sd.srp as srpExchange ,sd.net as NetExchange
from tbl_Sales_detail sd
where sd.salesid=@orno and p.salestype='return/exchange'


And this is the result:

poref     pdocodeReturn        itemname     QtyReturn	 price	NetReturn
0000032	     72225684478	cream milk 25g	    2		  25        50


I like to achieve something like where results for tbl_Sales_detail will be added on the result with is column alias name like below:

poref      pdocodeReturn	   itemname	QtyReturn	price	NetReturn  salesidExchange pdocodeExchange proddescExchange   QtyExchange	srpExchange  NetExchange	  	
0000032	     72225684478	cream milk 25g	    2		  25        50          0000032      72225684479    fibered rice 1sck       2           2000          4000 


Column in color red must be added on the result which is from tbl_Sales_detail table!
Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 10/01/2013 23:26:54

imrul
Starting Member

Bangladesh
36 Posts

Posted - 10/02/2013 :  00:56:01  Show Profile  Reply with Quote
Hi looks like p.poref and sd.salesid have the same record. If this is the case you can use the following query
select
p.poref as pofrefReturn
, p.productcode as pdocodeReturn
, p.itemname as itemnameReturn
, p.qty as QtyReturn
, p.price as priceReturn
, p.net as NetReturn
, sd.salesid as salesidExchange
, sd.productcode as pdocodeExchange
, sd.productdesc as proddescExchange
, sd.qty as QtyExchange
, sd.srp as srpExchange
, sd.net as NetExchange
from tbl_PO_detail p
inner join tbl_Sales_detail sd on sd.salesid=p.poref

where p.poref=@orno and p.memo='return/exchange'
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 10/04/2013 :  00:32:45  Show Profile  Reply with Quote
thank you imrul!

I did not notice that trick.. :) it works!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000