Urgent! Outer Join doesn't work.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Urgent! Outer Join doesn't work.

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    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.

  2. #2
    Join Date
    Dec 2000
    Posts
    87
    Correction t1 should be

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

    There was an extra c.

  3. #3
    Join Date
    Dec 2000
    Posts
    87
    The space didn't appear as it suppose to be.
    The numbers should in-line with the column 'authCountryid'.
    Thanks.

  4. #4
    Join Date
    Mar 2001
    Posts
    63

    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!


  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Try this

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

    Regards
    Gert

  6. #6
    Join Date
    Dec 2000
    Posts
    87
    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
  •  


Click Here to Expand Forum to Full Width