| View previous topic :: View next topic |
| Author |
Message |
somuk
Beginner
Joined: 04 Feb 2003
Posts: 113
Topics: 37
|
 Posted: Wed Aug 27, 2003 7:24 am Post subject: Help in SQL query |
 |
|
Hi ,
I have a table with Account_id and bank_no. Sample data is given below.
| Code: |
Account_id Bank_no
------------ ----------
1 115
1 294
2 115
2 294
3 294
4 115
5 110
5 112
|
1. I need to get all the Account_id which are common in both 115 and 294
Bank_no.
Result : 1 , 2
2. I have to get all the account_id which are unique for bank_no 294.. Means which are present in only for bank_no 294.
Result : 3
Can somebody help me on this query..?
Thanks
-Somu |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Wed Aug 27, 2003 7:52 am Post subject: |
|
|
| Code: |
1) SELECT T1.Account_id
FROM your_table T1
,your_table T2
WHERE T1.Bank_no = T2.Bank_no
AND T1.Account_id = T2.Account_id
AND T1.Bank_no in (115, 294);
|
| Code: |
2) SELECT T1.Account_id
FROM your_table T1
WHERE T1.Bank_no = 294
AND NOT EXISTS
(SELECT 1
FROM your_table T2
WHERE T2.Bank_no <> 294
AND T2.Account_id = T1.Account_id
);
|
regards
Christian |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 27, 2003 8:37 am Post subject: |
|
|
Somu,
Another way of getting the desired results.
1. | Code: |
SELECT ACCOUNT_ID
FROM TABLE
WHERE BANK_NO IN ('115','294')
GROUP BY ACCOUNT_ID HAVING COUNT(*) > 1
;
|
2. | Code: |
SELECT ACCOUNT_ID
FROM TABLE
WHERE ACCOUNT_ID NOT IN (SELECT ACCOUNT_ID
FROM TABLE
WHERE BANK_NO <> '294')
AND BANK_NO = '294'
;
|
Hope this helps...
cheers
kolusu |
|
| Back to top |
|
|
somuk
Beginner
Joined: 04 Feb 2003
Posts: 113
Topics: 37
|
| Posted: Thu Aug 28, 2003 12:16 am Post subject: |
|
|
Thanks Christian and Kolusu..
Regds
-Somu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
