Re: Need Help with SQL

From: Eugenio <eugenio.spadafora_nospam_at_compaq.com>
Date: 2000/07/04
Message-ID: <8jsptq$mbc$1@mailint03.im.hou.compaq.com>#1/1

To simplify the statements I created a view, but you can use the view's definition as inline view inside the other SQl statements.

the view v gives you all the pairs (ssn, acctno) in (table1 U table2)

create view v as
 select ssn, acctno from table1
  union all
 select ssn, acctno from table2

the following query gives you the SSNs with more than 1 account

select ssn
from v
group by ssn
having count(acctno) > 1

and the following query gives you the pairs (ssn, acctno) you're looking for

select v.ssn, v.acctno
from v,

     (select ssn
      from v
      group by ssn
      having count(acctno) > 1) v1

where v.ssn = v1.ssn

The query is complex (performance point of view) and I'm not sure that indexing (ssn, acctno) on both tables will help you. You should check it on your environment.

--
I hope this helps

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
senthil wrote in message <8jqm1a$tv5$1_at_nnrp1.deja.com>...


>hi,

>i hope this query may solve your requirement.

>

>select acctno from table1 group by ssn having count(*) > 1

>union

>select acctno from table1 where no in (select acctno from table2)

>union

>select acctno from table2 group by ssn having count(*) > 1

>union

>select acctno from table2 where no in (select acctno from table1);

>

>thanks,

>senthil

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.

Received on Tue Jul 04 2000 - 00:00:00 CDT