Self Join + outer join 1

[paljnad]

I have two tables that have columns as follows:

Table A has columns: UserID, Proxy ID

And the data in Table A looks like:

UserID Proxy ID
1 4
2 4
3 4


The data means that 4 is the proxy for userIDs 1, 2 and 3

The second table is Table B which has columns: UserId and User Name

And the data in Table B looks like:

User ID User Name
1 AB
2 CD
3 EF
4 FG

I am trying to join these two tables to get data like:

User Proxy
AB FG
CD FG
EF FG

I tried various queries but I seem to be getting only half the information that I want.

Thanks.

 

[SantaMufasa]

Interestingly, Paljnad, your query involves neither a self join nor an outer join:

SQL> select * from table_a;

    USERID    PROXYID
---------- ----------
         1          4
         2          4
         3          4
SQL> select * from table_b;

    USERID USER_
---------- -----
         1 AB
         2 CD
         3 EF
         4 FG

select b.user_name "User", p.user_name "Proxy"
  from table_a a, table_b b, table_b p
 where a.userid = b.userid
   and a.proxyid = p.userid
/

User  Proxy
----- -----
AB    FG
CD    FG
EF    FG

Let us know if this produces questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[paljnad]

Thank you, Santa Mufasa - that worked perfect !

I was also able to make it work with this:

select b.user_id MAINUSER,
a.user_id PROXY,
d.user_full_name PROXY_USERNAME,
e.user_full_name MAINUSER_NAME
from table_a a,
table_a b,
table_b d,
table_b e
where a.user_id = b.proxy_user_id(+)
and a.user_id = d.user_id
and b.user_id = e.user_id