Join 2 select Statements - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Join 2 select Statements

Author

Topic

nadeemymo
Starting Member

3 Posts

Posted - 2014-12-22 : 08:59:41

Hello , how can i join these 2 queries to produce 1 result
Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null

Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID

The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno

Field Saleid is present in Transactionheader Table and Sale table

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-22 : 09:32:14

Looking at the two queries, I cannot see what to join them on. What column would you use as the join column?

nadeemymo
Starting Member

3 Posts

Posted - 2014-12-22 : 09:40:53

i want to join these queries where the saleids are the same in both tables

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-22 : 10:21:25

OK then the simplest approach (may or may not be the best):


SELECT * FROM (
	select saleid, R.Name, T.Branchid, t.TradingDate,t. TransactionDate, 
	convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber, 
	t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
	from TransactionHeader T 
	join SalesRep R on
	R.SalesRepid = T.SalesRepid
	where T.SalesRepid is not NULL
) q1

JOIN (
	select saleid, C.TradingName,C.AccountNo
	From Sale S
	Join ClMast c on
	C.Clientid = s.CustomerAccountID
) q2

ON q1.salesid = q2.salesid

nadeemymo
Starting Member

3 Posts

Posted - 2014-12-23 : 02:50:06

the query runs but displays a blank result,the same problem i had with a different script.The queries on their own work fine

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-23 : 08:45:37

If you get no results, it means that there are no rows satisfying the on-condition . Check your data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources