simple query

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

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

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

Code:

---


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


---

You don't need the distinct or the subquery.

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

Code:

---


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.