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
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;