UNION inside UNION and JOIN table from different SELECT into one table

Question

I have query like this

This is my first query.

SELECT 
            id, 
            COUNT(id) ct_id, 
            SUM(kl) sum_kl, 
            SUM(CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
            SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
        FROM (
            SELECT 
                id_kondisi as id, 
                tgl_kondisi,
                nilai_potensi_kerugian AS kl 
            FROM laporan_kondisi 
            UNION  
            SELECT 
                id_sub_kondisi, 
                tgl_kondisi, 
                nilai_potensi_kerugian 
            FROM laporan_kondisi 
        ) merged_table  GROUP BY id ORDER BY id;

The result

+------+-------+----------+------+----------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  |
+------+-------+----------+------+----------+
| 01   |     3 | 20000000 |    3 | 20000000 |
| 0101 |     2 |  9000000 |    2 |  9000000 |
| 0102 |     2 | 11000000 |    2 | 11000000 |
| 02   |     1 |        0 |    0 |        0 |
| 0201 |     1 |        0 |    0 |        0 |
| 0202 |     1 |        0 |    0 |        0 |
| 0203 |     1 |        0 |    0 |        0 |
| 03   |     2 |  4000000 |    2 |  4000000 |
| 0301 |     1 |        0 |    1 |        0 |
| 0302 |     2 |  4000000 |    2 |  4000000 |
| 0303 |     1 |        0 |    1 |        0 |
+------+-------+----------+------+----------+

And the second query

SELECT  
        id,
        SUM(CASE WHEN tgl_tindak_lanjut="2017-09-14" THEN 1 ELSE 0 end ) as count_all_09,
        SUM( CASE WHEN tgl_tindak_lanjut="2017-09-13" THEN 1 ELSE 0 END) as count_09_13 
    FROM ( 
        SELECT 
            a.id_kondisi as id, 
            d.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a 
        LEFT OUTER JOIN  
            laporan_sebab b 
        ON 
            a.id = b.id_laporan_kondisi 
        LEFT OUTER JOIN  
            laporan_rekomendasi c 
        ON 
            b.id = c.id_laporan_sebab 
        LEFT OUTER JOIN 
            laporan_tindak_lanjut d 
        ON 
            c.id = d.id_laporan_rekomendasi  
        UNION  
        SELECT 
            a2.id_sub_kondisi,
            d2.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a2 
        LEFT OUTER JOIN 
            laporan_sebab b2 
        ON  
            a2.id = b2.id_laporan_kondisi 
        LEFT OUTER JOIN 
            laporan_rekomendasi c2 
        ON 
            b2.id = c2.id_laporan_sebab 
        LEFT OUTER join  
            laporan_tindak_lanjut d2 
        ON 
            c2.id = d2.id_laporan_rekomendasi  
    ) merged_table GROUP BY id

The Result

+------+--------------+-------------+
| id   | count_all_09 | count_09_13 |
+------+--------------+-------------+
| 01   |            0 |           1 |
| 0101 |            0 |           1 |
| 0102 |            0 |           0 |
| 02   |            0 |           0 |
| 0201 |            0 |           0 |
| 0202 |            0 |           0 |
| 0203 |            0 |           0 |
| 03   |            0 |           0 |
| 0301 |            0 |           0 |
| 0302 |            0 |           0 |
| 0303 |            0 |           0 |
+------+--------------+-------------+

I want to UNION the FIrst and second queries so the table will looks like this

+------+-------+----------+------+----------+--------------+-------------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  | count_all_09 | count_09_13 |
+------+-------+----------+------+----------+--------------+-------------+
| 01   |     3 | 20000000 |    3 | 20000000 |     0        |       1     |
| 0101 |     2 |  9000000 |    2 |  9000000 |     0        |       1     |
| 0102 |     2 | 11000000 |    2 | 11000000 |     0        |       0     |
| 02   |     1 |        0 |    0 |        0 |     0        |       0     |
| 0201 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0202 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0203 |     1 |        0 |    0 |        0 |     0        |       0     |
| 03   |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0301 |     1 |        0 |    1 |        0 |     0        |       0     |
| 0302 |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0303 |     1 |        0 |    1 |        0 |     0        |       0     |
+------+-------+----------+------+----------+--------------+-------------+

And my query is like this.

SELECT 
        id, 
        COUNT(id) ct_id, 
        SUM(kl) sum_kl, 
        SUM(CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
        SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
    FROM (
        SELECT 
            id_kondisi as id, 
            tgl_kondisi,
            nilai_potensi_kerugian AS kl 
        FROM laporan_kondisi 
        UNION  
        SELECT 
            id_sub_kondisi, 
            tgl_kondisi, 
            nilai_potensi_kerugian 
        FROM laporan_kondisi 
    ) merged_table 

UNION ALL

SELECT  
    id,
    SUM(CASE WHEN tgl_tindak_lanjut="2017-09-14" THEN 1 ELSE 0 end ) as count_all_09,
    SUM( CASE WHEN tgl_tindak_lanjut="2017-09-13" THEN 1 ELSE 0 END) as count_09_13 
FROM ( 
    SELECT 
        a.id_kondisi as id, 
        d.tgl_tindak_lanjut as tgl_tindak_lanjut 
    FROM
        laporan_kondisi a 
    LEFT OUTER JOIN  
        laporan_sebab b 
    ON 
        a.id = b.id_laporan_kondisi 
    LEFT OUTER JOIN  
        laporan_rekomendasi c 
    ON 
        b.id = c.id_laporan_sebab 
    LEFT OUTER JOIN 
        laporan_tindak_lanjut d 
    ON 
        c.id = d.id_laporan_rekomendasi  
    UNION  
    SELECT 
        a2.id_sub_kondisi,
        d2.tgl_tindak_lanjut as tgl_tindak_lanjut 
    FROM
        laporan_kondisi a2 
    LEFT OUTER JOIN 
        laporan_sebab b2 
    ON  
        a2.id = b2.id_laporan_kondisi 
    LEFT OUTER JOIN 
        laporan_rekomendasi c2 
    ON 
        b2.id = c2.id_laporan_sebab 
    LEFT OUTER join  
        laporan_tindak_lanjut d2 
    ON 
        c2.id = d2.id_laporan_rekomendasi  
) merged_table GROUP BY id

I want to UNION ALL when in my FROM there is an UNION, while in my UNION there is LEFT OUTER JOIN, when i am trying this query i got an error like this below

The used SELECT statements have a different number of columns

Is there anything wrong with my code ? or maybe i used query that can't be done? I guess that SQL fiddle is not needed for this, most likely experienced people will see right away what is wrong.

From JNevill Query, i got a result

+------+-------+----------+------+----------+----+--------------+-------------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  | id | count_all_09 | count_09_13 |
+------+-------+----------+------+----------+----+--------------+-------------+
| 01   |    17 | 48000000 |   13 | 48000000 | 01 |            0 |           1 |
+------+-------+----------+------+----------+----+--------------+-------------+
1 row in set (0.00 sec)

As you tagged your question with php, javascript and jquery...where are those codes? — B001ᛦ, Sep 21 '17 at 08:57
What if one of the datasets has more record than the other? Can you also please include the code you currently have that you are having issues with? — Nope, Sep 21 '17 at 08:57
You are trying to UNION together a result set with five columns `id, ct_id, sum_kl, tot, and sum_k12` with a result set that has 3 columns `id, count_all_09, and count_09_13`. Union stacks the results of the unioned queries on top of one another so stacking 5 columns on top of 3 columns is nonsense and the database is telling you so. **Perhaps instead of a UNION you wanted to JOIN the two result sets together on ID?** If you can share what your expected results look like, we can probably guide you towards a workable solution. — JNevill, Sep 21 '17 at 13:33

score 1 · Accepted Answer · answered Sep 21 '17 at 14:08

Instead of a Union you are looking for a Join. Union stacks the results of two queries on top of each other, where a JOIN lays the results of two queries (or tables or views) side by side JOINing ON a common key.

Something like the following should work for you

SELECT
    *
FROM
    (
        SELECT 
            id, 
            COUNT(id) ct_id, 
            SUM(kl) sum_kl, 
            SUM(CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
            SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
        FROM (
            SELECT 
                id_kondisi as id, 
                tgl_kondisi,
                nilai_potensi_kerugian AS kl 
            FROM laporan_kondisi 
            UNION  
            SELECT 
                id_sub_kondisi, 
                tgl_kondisi, 
                nilai_potensi_kerugian 
            FROM laporan_kondisi 
    ) merged_table 


    ) as t1
    JOIN
        (
            SELECT  
                id,
                SUM(CASE WHEN tgl_tindak_lanjut="2017-09-14" THEN 1 ELSE 0 end ) as count_all_09,
                SUM( CASE WHEN tgl_tindak_lanjut="2017-09-13" THEN 1 ELSE 0 END) as count_09_13 
            FROM ( 
                SELECT 
                    a.id_kondisi as id, 
                    d.tgl_tindak_lanjut as tgl_tindak_lanjut 
                FROM
                    laporan_kondisi a 
                LEFT OUTER JOIN  
                    laporan_sebab b 
                ON 
                    a.id = b.id_laporan_kondisi 
                LEFT OUTER JOIN  
                    lap oran_rekomendasi c 
                ON 
                    b.id = c.id_laporan_sebab 
                LEFT OUTER JOIN 
                    laporan_tindak_lanjut d 
                ON 
                    c.id = d.id_laporan_rekomendasi  
                UNION  
                SELECT 
                    a2.id_sub_kondisi,
                    d2.tgl_tindak_lanjut as tgl_tindak_lanjut 
                FROM
                    laporan_kondisi a2 
                LEFT OUTER JOIN 
                    laporan_sebab b2 
                ON  
                    a2.id = b2.id_laporan_kondisi 
                LEFT OUTER JOIN 
                    laporan_rekomendasi c2 
                ON 
                    b2.id = c2.id_laporan_sebab 
                LEFT OUTER join  
                    laporan_tindak_lanjut d2 
                ON 
                    c2.id = d2.id_laporan_rekomendasi  
            ) merged_table GROUP BY id


        ) as t2
        ON t1.id = t2.id

Check out the w3schools example for a quick very-high level primer on SQL joins.

the reason why it return only one row, because they sum the entire column i have edited my question a bit for the result — Gagantous, Sep 21 '17 at 14:54
I'm guessing that you don't have all ID's present in the result set of that second query to which we are joining the first. Instead of `JOIN` use `LEFT OUTER JOIN` This will take ALL records from the first query and any matching records (on `id`) from the second query. — JNevill, Sep 21 '17 at 15:54
Given the two outputs of the subqueries, I don't see how a result with one record is possible. I'm not sure if MySQL supports it, but perhaps try a FULL OUTER JOIN and see what kind of records you pick up with that. — JNevill, Sep 21 '17 at 16:00
it seems MySQL dont support full outer join . It give me error message `You have an error in your SQL syntax; check the manual bla bla` — Gagantous, Sep 21 '17 at 16:04
from this question, i have to use union https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql?answertab=oldest#tab-top — Gagantous, Sep 21 '17 at 16:06
Yea, that's a workaround if you know a FULL OUTER JOIN is what you need, but I'm nearly 100% certain it's not what you need, but it's useful for debugging a troublesome query like this. I believe an INNER JOIN (as written) or a LEFT OUTER JOIN is the right way to go given your sample data that you've posted in your question. I'm honestly at a loss as to why you are only getting a single record back when joining these two subqueries that clearly have MANY id's in common between them. — JNevill, Sep 21 '17 at 16:09
is there a way for add the table result of different query into result of other query ( like the result i expect in my question) without having the first and second query relate each other ? — Gagantous, Sep 21 '17 at 16:29
it's like, I "push" the table result of the second query into the table result of first query, and, just like that. — Gagantous, Sep 21 '17 at 16:31

UNION inside UNION and JOIN table from different SELECT into one table

1 Answers1