issue about SQL full join - 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)

issue about SQL full join

Author

Topic

lily
Starting Member

6 Posts

Posted - 2002-05-14 : 04:41:57

Hi,
I have two query as follow:

1.
select acctno,count(*) cnt into #a from test1group by acctno
select acctno,count(*) cnt into #b from test2 where accounting_id ='3280' and nsad<>nsbd
group by acctno

select #a.*,#b.* from
#a full join
#b
on #a.acctno = #b.acctno
where #b.acctno is null

2.
select * from
(select acctno ,count(*) cnt from test1 group by acctno ) a full join
(select acctno,count(*) cnt from test2 where accounting_id ='3280' and nsad<>nsbd
group by acctno) b
on a.acctno = b.acctno
where b.acctno is null

After join I should get acctno and cnt which are in test1 or test2

query 1: give correct result. the query full join a and b ,listed acctno either in table a or tableb.

query 2: That only listed acctno which are existing in both table.

How can I get correct result using query 2 instead of using hash table?
Thanks

rbharatha
Starting Member

29 Posts

Posted - 2002-05-14 : 06:09:30

Hi Lily,

If not wrong this would help you,

select isnull(a.acctno, b.acctno) acctno,
isnull(a.cnt, b.cnt) cnt
from
(select acctno, count(*) cnt from test1 group by acctno) a
full left join
(select acctno, count(*) cnt from test2
where accounting_id = '3280'
and nsad <> nsbd
group by acctno) b
on a.acctno = b.acctno

lily
Starting Member

6 Posts

Posted - 2002-05-16 : 07:33:19

Thanks for advise. I got it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources