Urgent! Outer Join doesn't work.

[coolmandba]

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.

[coolmandba]

Correction t1 should be

user
-----
a
b
c
d
e
f

There was an extra c.

[coolmandba]

The space didn't appear as it suppose to be.
The numbers should in-line with the column 'authCountryid'.
Thanks.

[Krutsinger]

... 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!

[denevge]

Try this

select t1.user,x2.authCountryid
from t1,(select * from t2 where authCountryid = 19 ) x2
where t1.user = x2.user (+)
/

Regards
Gert

[coolmandba]

Thanks all that helped.
It worked.