CODEWITHSUNDEEP
sql
Maco-ic
Maco-ic

Reputation: 99

How to join two queries in SQL

I have two SQL queries that I want to join.

First query:

SELECT 
    rp_date, key_code, 
    sum(case when rp_id=15102 then rp_value else null end) as users_completed, 
    sum(case when rp_id=15108 then rp_value else null end) as users_inProgress 
FROM
    te_rp_pc_rate
WHERE
    abc_code = 'A204'
    AND organisation_id = '444-4'
    AND key_ code = '#KL0560' 
GROUP BY
    rp_date, key_code 
ORDER BY 
    rp_date DESC, key_code 
LIMIT 100;

Second query:

SELECT 
    cr_date  
    sum(case when rp_id=23101 then rp_value else null end) AS prim_kfc 
FROM
    te_emk_rate
WHERE 
    abc_code = 'A204'
    AND organisation_id = '444-4' 
    AND ref_value = 0
GROUP BY
    cr_date 
ORDER BY 
    cr_date DESC 
LIMIT 100;

The dates (cr_date, rp_date) should be used for joining. For the first query's rp_date for the second cr_date.

The goal is to get the columns in a row for the same date. I've tried, but the results are too high.

Upvotes: 1

Views: 104

Answers (1)

Alan Millirud
Alan Millirud

Reputation: 1172

You can put your queries into subqueries and join them. Something like this:

SELECT *
FROM (
    SELECT rp_date, key_code, 
        sum(case when rp_id=15102 then rp_value else null end) as users_completed, 
        sum(case when rp_id=15108 then rp_value else null end) as users_inProgress 
    from te_rp_pc_rate
    WHERE abc_code = 'A204'
        AND organisation_id = '444-4'
        AND key_ code = '#KL0560' 
    group by rp_date, key_code 
    Order By rp_date DESC, key_code 
    LIMIT 100;
) as q1

LEFT JOIN (
    SELECT cr_date, key_code  
    sum(case when rp_id=23101 then rp_value else null end) as prim_kfc 
    from te_emk_rate
    WHERE abc_code = 'A204'
    AND organisation_id = '444-4' 
    AND ref_value = 0
    group by cr_date, key_code 
    Order By cr_date DESC, key_code 
    LIMIT 100;
) as q2
on q1.rp_date = q2.cr_date

Upvotes: 5

Related Questions