simple query

[kris123]

Hi Kuya,

I have a TRANSACT table with the following info:

ACCT_NO NAME
------- ----------
1001 SCOTT
1001 SCOTT
1002 STEVE
1002 TOM

I want to list(select) the acct_no being used by more than 1 person,
in this case its (1002).

Can u help me pls...
Sorry my mind does not work well these days...anyway im still trying.

Thanks a lot

[kris123]

Hi Kuya,

I got it...

select acct_no,name from TRANSACT where (acct_no,name) in
(select acct_no,name from(select distinct acct_no,name from TRANSACT)
group by acct_no,name having count(*)>1);

Are there other better ones pls...

Thanks

[gandolf989]

Is there a reason that you don't do this?

SELECT acct_no, name 
  FROM TRANSACT 
 GROUP BY acct_no, name 
HAVING COUNT(*)>1;

You don't need the distinct or the subquery.

[kris123]

im confused....i just thot this will hit this...

ACCT_NO NAME
------- ----------
1001 SCOTT
1001 SCOTT

and not this one...

ACCT_NO NAME
------- ----------
1002 STEVE
1002 TOM

[abhaysk]

Select 
  A.* 
From
  (Select distinct acct_no, name From Transact) A,
  (Select distinct acct_no, name From Transact) B
Where
  A.acct_no = B.acct_no And
  A.name <> B.name
;

Abhay.