oracle - Join two tables when there is no value in one table

Question

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 make this string for a particular PS_SEQ using where clause.

(Account + ":" + ROLE.SOURCE + "|" + Manager + ":" + ROLE.SOURCE + "|" + Sales + ":" + ROLE.SOURCE)

Sometimes, ROLE table does not have whole ROLE_TYPE, for example, it only has Account and Manager. In that case, ROLE.SOURCE of Sales become just "". And, ROLE_DESC is dynamic, the number of entities can be changed.

Is there any way I can query this easily?

Answer 1

You can try this:

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)