How to get list of ids into separate column

CREATE TABLE master_tab (
    e_id      NUMBER(10),
    owner_id  NUMBER(10),
    CONSTRAINT pk_master_tab PRIMARY KEY(e_id)
);

CREATE TABLE transaction_tab (
    e_id      NUMBER(10),
    analysis_comp  NUMBER(10),
    CONSTRAINT fk_master_tab FOREIGN KEY(e_id) REFERENCES master_tab(e_id)
);

INSERT INTO master_tab VALUES(1,72);
INSERT INTO master_tab VALUES(2,72);
INSERT INTO master_tab VALUES(3,73);
INSERT INTO master_tab VALUES(4,null);

INSERT INTO transaction_tab VALUES(1,1);
INSERT INTO transaction_tab VALUES(2,1);
INSERT INTO transaction_tab VALUES(3,1);
INSERT INTO transaction_tab VALUES(4,1);

Problem statement: I have a table master_tab which consist of two columns e_id and owner_id. I want to list down the e_id’s which belong to owner_id 72 and 73. There is another condition that analysis_comp flag should be 1 in the table transaction_tab.

My attempt:

WITH a AS(
SELECT mt.e_id FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 72
),
b AS(
SELECT mt.e_id FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 73
)
SELECT a.e_id a_eid,b.e_id b_eid FROM a
FULL OUTER JOIN b ON(b.e_id = a.e_id);

Tool used: SQL Developer(18c)

Current Output:

+-------+-------+
| A_EID | B_EID |
+-------+-------+
| 1     | null  |
| 2     | null  |
| null  | 3     |
+-------+-------+

Expected Output:

+-------+-------+
| A_EID | B_EID |
+-------+-------+
|     1 |     3 |
|     2 |       |
+-------+-------+

Is it possible to get only the list of e_id when owner_id is 72 then in the first row it should give all the list of e_id whose owner_id is 72, Likewise, if owner_id is 73 then it should provide a separate list of e_id into another column? If I use the JOIN condition, it will restrict only to owner_id 72. Is there any other way to handle such scenarios where we need list of ids from the same table in different columns

Answer

@Vicky, this version is modified from you query and may be more comfortable for you.

WITH a AS(
SELECT mt.e_id, rownum r FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 72
),
b AS(
SELECT mt.e_id, rownum r FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 73
)
SELECT a.e_id a_eid, b.e_id b_eid from a full outer join b
on a.r=b.r ;

This version used ROWNUM for each subquery, then match the 2 columns row-by-row.

Why we have used row_number / rownum? Because you are joining the 2 columns using row number.

In SQL (or any table based output), it is generally expected that the data in a row is related to each other. You presentation may be confusing to those accustomed to this. Why not output separately?