How to get list of ids into separate column-CodePudding

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

CodePudding user response：

If I understand correctly, we can try to use the ROW_NUMBER window function with the condition aggregate function.

We can use JOIN on two tables by e_id and your expected logic then use ROW_NUMBER window function to get row number for each owner_id which calls rn columns

Final use condition aggregate function with group by rn to get owner_id are 72 , 73 of e_id

Query 1:

SELECT MAX(CASE WHEN owner_id = 72 THEN e_id END) A_id,
       MAX(CASE WHEN owner_id = 73 THEN e_id END) B_id
FROM (
  SELECT owner_id,
         mt.e_id,
         ROW_NUMBER() OVER(PARTITION BY owner_id ORDER BY mt.e_id) rn
  FROM master_tab mt 
  INNER JOIN transaction_tab tt 
  ON tt.e_id = mt.e_id
  WHERE tt.analysis_comp = 1 AND owner_id IN (72,73)
) t1
GROUP BY rn

Results:

| A_ID |   B_ID |
|------|--------|
|    1 |      3 |
|    2 | (null) |

CodePudding user response：

@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?