Hi im looking for a query which will give me both common and non-common data in one query.

Table 2

Result

Based on req_ind column from table 1 , if req_ind is 1 and the same assay is present in table 2 i want to list it as above.

required missing column can have multiple column.

CodePudding user response：

With the data given this gives requested result:

WITH table1 as (
   select 1 as ID, 123 as Assay, 0 as req_ind from dual
   union all
   select 2,124,1 from dual
   union all
   select 3,125,1 from dual
   ),
table2 as (
   select 1 as ID, 124 as Assay from dual
),
required_missing as (
   select 
      row_number() over (order by table1.Assay) as R,
      table1.Assay as required_missing
   from table1
   left join table2 on table2.Assay = table1.Assay
   where table1.req_ind=1 and table2.id is null
),
requires_present as (
   select 
      row_number() over (order by table1.Assay) as R,
      table1.Assay as required_present
   from table1
   left join table2 on table2.Assay = table1.Assay
   where table1.req_ind=1 and table2.id is not null
),
results as (
   select row_number() over (order by (id)) as r
   from table1
)
select rm.required_missing, rp.required_present
from results
left join required_missing rm on rm.R = results.R
left join requires_present rp on rp.R = results.R
where rm.R is not null or rp.R is not null;

output:

CodePudding user response：

If you want to have a comma separated list for missing and for present then you can use:

SELECT LISTAGG(CASE WHEN t2.assay IS NULL THEN t1.assay END, ',')
         WITHIN GROUP (ORDER BY t1.assay) AS required_missing,
       LISTAGG(t2.assay, ',')
         WITHIN GROUP (ORDER BY t1.assay) AS required_present
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON (t1.assay = t2.assay)
WHERE  t1.req_ind = 1

Which, for the sample data:

CREATE TABLE table1 (id, assay, req_ind) AS
SELECT 1, 123, 0 FROM DUAL UNION ALL
SELECT 2, 124, 1 FROM DUAL UNION ALL
SELECT 3, 125, 1 FROM DUAL UNION ALL
SELECT 4, 126, 1 FROM DUAL UNION ALL
SELECT 5, 127, 1 FROM DUAL;

CREATE TABLE table2 (id, assay) AS
SELECT 1, 124 FROM DUAL UNION ALL
SELECT 2, 127 FROM DUAL;

Outputs:

REQUIRED_MISSING	REQUIRED_PRESENT
125,126	124,127

If you want the output in multiple rows then:

SELECT required_missing,
       required_present
FROM   (
  SELECT NVL2(t2.assay, 'P', 'M') AS status,
         ROW_NUMBER() OVER (
           PARTITION BY NVL2(t2.assay, 'P', 'M')
           ORDER BY t1.assay
         ) AS rn,
         t1.assay
  FROM   table1 t1
         LEFT OUTER JOIN table2 t2
         ON (t1.assay = t2.assay)
  WHERE  t1.req_ind = 1
)
PIVOT (
  MAX(assay)
  FOR status IN (
    'M' AS required_missing,
    'P' AS required_present
  )
)

Which outputs:

REQUIRED_MISSING	REQUIRED_PRESENT
125	124
126	127

db<>fiddle here