FULL JOIN : Select the key I join on

Question

Here's the table I start with

CMS   |   defect_status 
________________________
1     |   true
2     |   false
3     |   true
3     |   false

and here's what I want

   CMS    |      true_defects    |   false_defects
   1      |      1               |   0
   2      |      0               |   1
   3      |      1               |   1

So here's my code

SELECT DISTINCT
    false_table.CMS, 
    true_table.true_defects,
    false_table.false_defects

FROM(
    SELECT DISTINCT 
        CMS,
        COUNT(*) AS true_defects 
    FROM data_table
    WHERE defect_status = 'true'
    GROUP BY CMS 
) as true_table

FULL JOIN(
    SELECT DISTINCT 
        CMS,
        COUNT(*) AS false_defects 
    FROM data_table
    WHERE defect_status = 'false'
    GROUP BY CMS 
) as false_table
ON true_table.CMS = false_table.CMS

I would like to select all CMS, those in "false_table" and "true_table". If I select "false_table.CMS" (as in the code above), here's what I get :

CMS    |      true_defects    |   false_defects
2      |      0               |   1
3      |      1               |   1

CMS "1" has disappeared simply because it's not in the column false_table.CMS

Thank you

Have you tried using a case statement instead and sum the results? Using a full join here is a bit strange. — Andrew, Jan 24 '18 at 09:53
According to the duplicate question, full join isn't possible in mysql. How are you getting results? Are you sure you're using mysql? — HoneyBadger, Jan 24 '18 at 09:54
If this is not mysql, can you tag this with the correct database, and we can attempt to get it re-opened. — Andrew, Jan 24 '18 at 10:00
I think it is not the duplicate one, it is totally different one.Maybe tag added wrongly — Mittal Patel, Jan 24 '18 at 10:03
@a_horse_with_no_name IT's actually postgressql, just tagged it — Jdoe, Jan 24 '18 at 10:04
@Jdoe: You can achieve it by case and group by : demo link : http://sqlfiddle.com/#!17/8478d/1 — Mittal Patel, Jan 24 '18 at 10:07

score 1 · Accepted Answer · answered Jan 24 '18 at 10:09

You can achieve it by simple case and group by as below:

SELECT t1.CMS
    ,sum(CASE defect_status
            WHEN true
                THEN 1
            ELSE 0
            END) AS true_status
    ,sum(CASE defect_status
            WHEN false
                THEN 1
            ELSE 0
            END) AS false_status
FROM Table1 AS t1
GROUP BY CMS

Demo link: http://sqlfiddle.com/#!17/8478d/1

FULL JOIN : Select the key I join on

1 Answers1