-
Hi all,
I have two tables as:
The 1st table, t1, which has only one column called 'user':
select * from t1;
user
-----
a
b
c
d
c
e
f
The 2nd table t2:
user authCountryid
----- ----------
a 1
a 19
b 1
b 2
b 15
c 19
d 19
e 5
e 8
f 19
f 3
I'd like to get:
user authCountryid
----- ---------------
a 19
b
c 19
d 19
c
e
f 19
So I write the query as
select t1.user,t2.authCountryid
from t1,t2
where t1.user(+)=t2.user
and t2.authCountryid=19;
It only returns me:
user authCountryid
----- ---------------
a 19
c 19
d 19
f 19
Even if I switch the (+) to the right as t2.user(+), I yield the same result. It's strange.
Please advise.
-
Correction t1 should be
user
-----
a
b
c
d
e
f
There was an extra c.
-
The space didn't appear as it suppose to be.
The numbers should in-line with the column 'authCountryid'.
Thanks.
-
It's not pretty but...
... it will work:
SELECT DISTINCT T1.USER,
DECODE(T2.authCountryid, 19, '19', NULL)
FROM T1, T2
WHERE
T1.USER = T2.USER AND
(T1.USER, T2.authCountryid ) IN
(SELECT USER, MAX(authCountryid )
FROM T2
GROUP BY USER)
Enjoy!
-
Try this
select t1.user,x2.authCountryid
from t1,(select * from t2 where authCountryid = 19 ) x2
where t1.user = x2.user (+)
/
Regards
Gert
-
Thanks all that helped.
It worked.
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
|