join 2 queries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

join 2 queries

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-23 : 05:44:56

Hi,

I have the following 2 queries that i need to put into one select and show the results as follows:
ref 6 statrecint valuedoc valuestm
456 456 12,000 -12,000
345 345 11,000 -11,000

The 2 queries that i have are as follows:
select cast(ref6 as int) ref6,sum(valuedoc)
from oas_docline (nolock)
where cmpcode='KP2' and doccode in ('BANKRECON') and statrec=86
and cast(ref6 as int)> '57691'--'000000057691'
group by ref6
order by ref6

________
select statrecint, sum(valuestm)
from oas_stmdocline
where cmpcode='KP2' and valdate = '2007-01-01' and statrec=86
group by statrecint
order by statrecint

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 05:47:51

How about putting UNION ALL in between these two queries?

Select * from 
(
select cast(ref6 as int) ref6,sum(valuedoc)
from oas_docline (nolock)
where cmpcode='KP2' and doccode in ('BANKRECON') and statrec=86
and cast(ref6 as int)> '57691'--'000000057691'
group by ref6
union all
select statrecint as ref6, sum(valuestm)
from oas_stmdocline
where cmpcode='KP2' and valdate = '2007-01-01' and statrec=86
group by statrecint
) t
order by 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 05:49:53

looks like a INNER JOIN to me than a UNION


select *
from 
(
    <your 1st query here>
) q1
inner join
(
    <your 2nd query here> 
) q2
on q1.ref6 = q2.ref6

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-25 : 02:17:14

thanks for the replies. What does order by 1 mean?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 02:25:46

quote:
Originally posted by collie

thanks for the replies. What does order by 1 mean?

thanks

1st column in the select statement

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-25 : 04:13:16

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources