| View previous topic :: View next topic |
| Author |
Message |
dohellwithmf
Beginner
Joined: 12 Jul 2007
Posts: 55
Topics: 23
|
 Posted: Thu Sep 11, 2008 1:58 am Post subject: Problem in join |
 |
|
I want to fetch all USER_IDs for which TYPE_CD is NOT equal to 010. Is the following query correct one:
| Code: |
SELECT T1.TYPE_CD,T1.AS_OF_DATE,T2.USER_ID FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON (T1.BANK_ID = T2.BANK_ID AND
T1.BRANCH_ID = T2.BRANCH_ID AND
T1.ACCOUNT_ID = T2.ACCOUNT_ID)
WHERE T2.USER_ID NOT IN (SELECT T2.USER_ID FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON (T1.BANK_ID = T2.BANK_ID AND
T1.BRANCH_ID = T2.BRANCH_ID AND
T1.ACCOUNT_ID = T2.ACCOUNT_ID)
WHERE T1.TYPE_CD = '010') |
|
|
| Back to top |
|
 |
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Thu Sep 11, 2008 11:11 am Post subject: |
|
|
I might be missing something, but I don't see the need for the sub-select. Rather than NOT IN, why not use not equal?
| Code: | SELECT T1.TYPE_CD,T1.AS_OF_DATE,T2.USER_ID FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON (T1.BANK_ID = T2.BANK_ID AND
T1.BRANCH_ID = T2.BRANCH_ID AND
T1.ACCOUNT_ID = T2.ACCOUNT_ID)
WHERE T1.TYPE_CD <> '010' |
|
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Fri Sep 12, 2008 8:23 pm Post subject: |
|
|
maybe interview question??
anyway, both appear to be same and answer to the original question is yes. |
|
| Back to top |
|
|
dohellwithmf
Beginner
Joined: 12 Jul 2007
Posts: 55
Topics: 23
|
| Posted: Sun Sep 14, 2008 11:15 pm Post subject: |
|
|
I want to exclude all those USER_IDs which have TYPE_CD = '010'. Taking the following scenario:
| Code: |
USER_ID TYPE_CD
AAA 100
AAA 400
AAA 010
BBB 040
BBB 050
CCC 015
CCC 100
DDD 015
DDD 010
|
The above query will fetch all the USER_IDs but I don't want AAA and DDD in my output. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Sep 14, 2008 11:47 pm Post subject: |
|
|
dohellwithmf,
Unless I am missing something isnt it a simple case of subselect?
Try this untested sql
| Code: |
SELECT *
FROM TABLE
WHERE USER_ID NOT IN (SELECT USER_ID
FROM TABLE
WHERE TYP_CD = '010')
; |
Hope this helps...
Cheers
Kolusu |
|
| Back to top |
|
|
dohellwithmf
Beginner
Joined: 12 Jul 2007
Posts: 55
Topics: 23
|
| Posted: Mon Sep 15, 2008 12:30 am Post subject: |
|
|
| Isn't it the same I mentioned in the Question? USER_ID and TYPE_CD belong to two different table so I used join. Am I correct? |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
