How to join two query result and filter out the data based on the condition?-CodePudding

I am running two sql queries which is giving some output. I want to combine them and filter out the result based on the requirement.

Explaination -

query 1:

select `Project Number` from vw_onco_pharma onco_pharma union select `Project Number` from vw_onco_cell_gene cell_gene union select `Project Number` from vw_non_onco_cell_gene onco_cell_gene union select `Project Number` from vw_non_onco_pharma non_onco_pharma union select `Project Number` from vw_plasma_protein plasma_protein

Output 1:

Project Number
S100
S100
S200
S300
S300
S300
S400
S400

Query 2: select Project Number from FCT_HTA_ONC_NONONC_PGMS;

Output 2:

Project Number
S100
S200
S200
S300
S300
S300
S500

Now I have to convert Output1 & Output2 in below format:

Output 1 :                          Output 2 :
Project Number    Count            Project Number    Count
S100              2                S100              1    
S200              1                S200              2   
S300              3                S300              3
S400              2                S500              1

Now match the Output 1 & Output 2 and print the differences-

Final Output:

Project Number
S100
S200
S400
S500

CodePudding user response：

WITH
-- first query, UNION ALL used instead of single UNION 
cte1 AS (select `Project Number` from vw_onco_pharma onco_pharma 
         union all
         select `Project Number` from vw_onco_cell_gene cell_gene 
         union all
         select `Project Number` from vw_non_onco_cell_gene onco_cell_gene 
         union all
         select `Project Number` from vw_non_onco_pharma non_onco_pharma 
         union all
         select `Project Number` from vw_plasma_protein plasma_protein),
-- count the amounts for 1st query
cte2 AS (SELECT `Project Number`, COUNT(*) cnt
         FROM cte1
         GROUP BY `Project Number`),
-- 2nd query, amounts counting added
cte3 AS (select `Project Number`, COUNT(*) cnt
         from FCT_HTA_ONC_NONONC_PGMS
         GROUP BY `Project Number`),
-- gathering all projects
cte4 AS (SELECT `Project Number`
         FROM cte1
         UNION 
         SELECT `Project Number`
         FROM cte3)
-- get final data
SELECT `Project Number`
FROM cte4
LEFT JOIN cte2 USING (`Project Number`)
LEFT JOIN cte4 USING (`Project Number`)
WHERE cte2.cnt IS NULL
   OR cte4.cnt IS NULL
   OR cte2.cnt <> cte4.cnt;