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?

I don't understand the issue, what do you expect to get instead of "" ? — sagi, May 04 '16 at 12:36
If there is no value for Sales, it should print like Account:PPP|Manager:TTT|Sales:| — Anna Lee, May 04 '16 at 12:40
If there is no value for Manager, it should print like this Account:PPP|Manager:|Sales:RRR. — Anna Lee, May 04 '16 at 12:41
Actually, I can't make the query for printing that, so I am asking you. — Anna Lee, May 04 '16 at 12:45
@YoYo Are you sure? Come someone and immediately POSSIBLE DUPLICATE or DOWN VOTE. — Mottor, May 04 '16 at 15:34
Not exactly duplicate, but refer to answers 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:49

Aleksej · Answer 1 · 2016-05-04T14:58:41.477

0

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)

edited May 04 '16 at 14:58

answered May 04 '16 at 13:25

Aleksej

22,443
5
33
38

The role_desc table is dynamic, so I can't make it static. – Anna Lee May 04 '16 at 14:56
Please edit this question with the sample data you posted in the other one, then please close it, because it's a duplicate. After reading your second question, I edited y answer – Aleksej May 04 '16 at 14:58

score 0 · Answer 2 · answered May 04 '16 at 15:12

SELECT LISTAGG (role_name || ':' || source, '|') WITHIN GROUP (ORDER BY role_name)
  FROM (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)

Join two tables when there is no value in one table

2 Answers2

Linked