mysqlsqldatabaseqsqlquery

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


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

Solution

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