Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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  
 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.04 seconds. Powered By: Snitz Forums 2000