-1

I have two tables, ROLE and ROLE_DESC.

ROLE looks like this.

PS_ROLE_SEQ  NUMBER,    
PS_SEQ       NUMBER,    
ROLE_TYPE            VARCHAR2(2 CHAR),    
SOURCE               VARCHAR2(128 CHAR)

ROLE_DESC looks like this.

ROLE_TYPE  VARCHAR2(2 CHAR),    
ROLE_NAME  VARCHAR2(16 CHAR)

As you expect, ROLE_TYPE of ROLE and ROLE_TYPE of ROLE_DESC reference each other. There are three entities in ROLE_DESC, like this.

ROLE_TYPE   ROLE_NAME
A           Account
M           Manager
S           Sales

I would like to print whole role_type and role_name by joining two tables even thought ROLE table does not have a certain ROLE_TYPE. For example, if there is no 'S' role_type for PS_SEQ = 111. I tried, this, but I only got below.

select ROLE_DESC.ROLE_TYPE, ROLE_DESC.ROLE_NAME, ROLE.SOURCE 
FROM ROLE_DESC
LEFT OUTER JOIN ROLE
ON ROLE_DESC.role_type=ROLE.role_type
where ROLE.PS_SEQ = 111

Result:

A   Account Bob
M   Manager Sandy

Actually, I would like to get following.

A   Account Bob
M   Manager Sandy
S   Sales

Does outer join does make it possible? Could you please give some clue for this question?

Aleksej
  • 22,443
  • 5
  • 33
  • 38
Anna Lee
  • 909
  • 1
  • 17
  • 37
  • 2
    Isn't it the same question than [this](http://stackoverflow.com/questions/37027903/join-two-tables-when-there-is-no-value-in-one-table)? – Aleksej May 04 '16 at 14:53
  • It is different, but the purpose is same. if you want, I can delete the previous question. I am sorry for being confusion. – Anna Lee May 04 '16 at 14:57
  • 1
    I suggest to edit the first question, then close this – Aleksej May 04 '16 at 14:59
  • Possible duplicate of [How to use oracle outer join with a filter where clause](http://stackoverflow.com/questions/18390588/how-to-use-oracle-outer-join-with-a-filter-where-clause) – YoYo May 04 '16 at 15:17

1 Answers1

0
select ROLE_DESC.ROLE_TYPE, ROLE_DESC.ROLE_NAME, ROLE.SOURCE
  FROM ROLE_DESC
  LEFT OUTER JOIN ROLE
ON ROLE_DESC.role_type=ROLE.role_type AND ROLE.PS_SEQ = 111

When it is in WHERE clause, you filters the result. And in the result ROLE.PS_SEQ is Null for Sales and Null <> 111

Mottor
  • 1,938
  • 3
  • 12
  • 29