-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|