MySQL - Join With Default Value

Question

I have standard data that came from MySql statement

SELECT tipe.* 
  FROM 
     ( SELECT 'Menunggu Approve Atasan' AS tipe UNION
       SELECT 'Sudah Approved Atasan' UNION
       SELECT 'Dalam Proses Pengerjaan IT' UNION
       SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION
       SELECT 'Ditutup'
      ) AS tipe;
+---------------------------------------+
| tipe                                  |
+---------------------------------------+
| Menunggu Approve Atasan               |
| Sudah Approved Atasan                 |
| Dalam Proses Pengerjaan IT            |
| Sudah Selesai Tapi Belum Ditutup User |
| Ditutup                               |
+---------------------------------------+
5 rows in set (0.02 sec)

Then I have data like this

SELECT requests.* 
  FROM 
     ( SELECT DATE(request.tanggal_permintaan) as tanggal_permintaan
       , request.`status`
       , COUNT(*) as total
    FROM request
   WHERE YEAR(request.tanggal_permintaan) = '2018'
   GROUP 
    BY DATE(request.tanggal_permintaan)
       , request.status
      ) AS requests;
    +--------------------+---------------------------------------+-------+
    | tanggal_permintaan | status                                | total |
    +--------------------+---------------------------------------+-------+
    | 2018-01-02         | Menunggu Approve Atasan               |     1 |
    | 2018-01-02         | Ditutup                               |     4 |
    | 2018-01-03         | Ditutup                               |     1 |
    +--------------------+---------------------------------------+-------+
3 rows in set (0.02 sec)

As you can see form result of the second query, it gives me data based on date. But, I need to include all the standard data in those second query. I need result like this:

+--------------------+---------------------------------------+-------+
| tanggal_permintaan | status                                | total |
+--------------------+---------------------------------------+-------+
| 2018-01-02         | Menunggu Approve Atasan               |     1 |
| 2018-01-02         | Sudah Approved Atasan                 |     0 |
| 2018-01-02         | Dalam Proses Pengerjaan IT            |     0 |
| 2018-01-02         | Sudah Selesai Tapi Belum Ditutup User |     0 |
| 2018-01-02         | Ditutup                               |     4 |  // one day
| 2018-01-03         | Menunggu Approve Atasan               |     0 |
| 2018-01-03         | Sudah Approved Atasan                 |     0 |
| 2018-01-03         | Dalam Proses Pengerjaan IT            |     0 |
| 2018-01-03         | Sudah Selesai Tapi Belum Ditutup User |     0 |
| 2018-01-03         | Ditutup                               |     1 | // one day
+--------------------+---------------------------------------+-------+

What query that I use to get like this ? So far, I just try the left outer join but no luck

SELECT requests.* FROM (
    SELECT
        DATE(request.tanggal_permintaan) as tanggal_permintaan,
            request.`status`,
            COUNT(*) as total
    FROM request
    WHERE YEAR(request.tanggal_permintaan) = '2018'
    GROUP BY DATE(request.tanggal_permintaan), request.status
) requests

LEFT OUTER JOIN (
    SELECT tipe.* FROM(
        SELECT 'Menunggu Approve Atasan' AS tipe UNION
        SELECT 'Sudah Approved Atasan' UNION 
        SELECT 'Dalam Proses Pengerjaan IT' UNION 
        SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
        SELECT 'Ditutup'
    ) tipe
)standard 
ON standard.tipe = requests.status

ORDER BY requests.tanggal_permintaan

score 2 · Answer 1 · answered Nov 12 '18 at 05:35

To get that output, you need to create a CROSS JOIN of your tipe values with each of the distinct dates in your request table:

SELECT r.date, t.tipe
FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
      SELECT 'Sudah Approved Atasan' UNION 
      SELECT 'Dalam Proses Pengerjaan IT' UNION 
      SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
      SELECT 'Ditutup'
     ) t
CROSS JOIN 
    (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
     FROM request
     WHERE YEAR(tanggal_permintaan) = 2018) r

This will give you a table that looks like this:

| date               | tipe                                  |
+--------------------+---------------------------------------+
| 2018-01-02         | Menunggu Approve Atasan               |
| 2018-01-02         | Sudah Approved Atasan                 |
| 2018-01-02         | Dalam Proses Pengerjaan IT            |
| 2018-01-02         | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-02         | Ditutup                               |
| 2018-01-03         | Menunggu Approve Atasan               |
| 2018-01-03         | Sudah Approved Atasan                 |
| 2018-01-03         | Dalam Proses Pengerjaan IT            |
| 2018-01-03         | Sudah Selesai Tapi Belum Ditutup User |
| 2018-01-03         | Ditutup                               |

You can then LEFT JOIN that to your requests table to get the result you want:

SELECT s.date, s.tipe, COUNT(*) AS total
FROM (SELECT r.date, t.tipe
      FROM (SELECT 'Menunggu Approve Atasan' AS tipe UNION
            SELECT 'Sudah Approved Atasan' UNION 
            SELECT 'Dalam Proses Pengerjaan IT' UNION 
            SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION 
            SELECT 'Ditutup'
           ) t
      CROSS JOIN 
          (SELECT DISTINCT DATE(tanggal_permintaan) AS date 
           FROM request
           WHERE YEAR(tanggal_permintaan) = 2018) r
     ) s
LEFT JOIN request rq ON rq.status = s.tipe
GROUP BY s.date, s.tipe

Basically you give me a Enlightenment. Thanks – Fadly Dzil Nov 12 '18 at 06:01 — Fadly Dzil, Nov 12 '18 at 06:01

Madhur Bhaiya · Answer 2 · 2018-11-12T05:43:33.287

We can get all unique dates in the year 2018 in a Derived table. Year() function on the data does not allow it to use Indexes. I have changed it to use Between.. so that it can use the index.
Similarly, get all unique statuses in a separate Derived Table.
Cross Join between them to get all the possible combinations.
Now, use this all_combinations and left join to your main query, on status and date, to get the required result-set.

Try the following:

SELECT all_combinations.tanggal_permintaan, 
       all_combinations.tipe, 
       COALESCE(requests.total, 0) AS total 
FROM 
(
  SELECT all_tanggal_permintaan.tanggal_permintaan, 
         all_tipe.tipe 
  FROM 
  (SELECT DISTINCT DATE(tanggal_permintaan) as tanggal_permintaan
   FROM request 
   WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
                                    '2018-12-31 23:59:59'
  ) AS all_tanggal_permintaan 

  CROSS JOIN 

  (SELECT 'Menunggu Approve Atasan' AS tipe UNION ALL
   SELECT 'Sudah Approved Atasan' UNION ALL
   SELECT 'Dalam Proses Pengerjaan IT' UNION ALL
   SELECT 'Sudah Selesai Tapi Belum Ditutup User' UNION ALL
   SELECT 'Ditutup'
  ) AS all_tipe
) AS all_combinations 

LEFT OUTER JOIN

(
  SELECT
    DATE(tanggal_permintaan) as tanggal_permintaan,
    status,
    COUNT(*) AS total 
  FROM request
  WHERE tanggal_permintaan BETWEEN '2018-01-01 00:00:00' AND 
                                   '2018-12-31 23:59:59'
  GROUP BY DATE(tanggal_permintaan), status
) AS requests 
ON requests.tanggal_permintaan = all_combinations.tanggal_permintaan AND 
   requests.status = all_combinations.tipe

ORDER BY all_combinations.tanggal_permintaan

Paul Maxwell · Answer 3 · 2018-11-12T05:58:35.393

1

This is a guess, but it seems you need a list of status values, and it surprises me this isn't already in a table, but this might work:

SELECT
    g.tanggal_permintaan, g.total, s.status
FROM (
    SELECT DISTINCT
        request.status
    FROM request
) s
LEFT JOIN (
        SELECT
            DATE(request.tanggal_permintaan) AS tanggal_permintaan
           ,request.status
           ,COUNT(*)                         AS total
        FROM request
        WHERE request.tanggal_permintaan) >= '20180101'
          AND request.tanggal_permintaan) <  '20190101'
        GROUP BY
            DATE(request.tanggal_permintaan)
           ,request.status
    ) g
    ON s.status = g.status

edited Nov 12 '18 at 05:58

answered Nov 12 '18 at 05:31

Paul Maxwell

33,002
3
32
51

I am sorry brother, it not works. Still default list not loaded – Fadly Dzil Nov 12 '18 at 05:35
OK, I suggest you try the query by Nick. But please avoid using functions on date columns like this `YEAR(request.tanggal_permintaan) = '2018'` see my query above. also see https://stackoverflow.com/a/25564544/2067753 – Paul Maxwell Nov 12 '18 at 05:47

score 0 · Answer 4 · answered Nov 12 '18 at 06:03

Thanks for brother @Nick and @Used_By_Already

Here it is my final query. I create a unique id based concat to connecting them via left join.

SELECT standard.*, transactions.* FROM (
    SELECT CONCAT(r.date, '#', t.tipe) as 'id', r.date AS tanggal_permintaan, t.tipe
    FROM (
         SELECT DISTINCT
             request.status AS tipe
         FROM request
    ) t
    CROSS JOIN (
        SELECT DISTINCT DATE(tanggal_permintaan) AS date 
        FROM request
        WHERE YEAR(request.tanggal_permintaan) = '2018' 
    ) r

) AS standard

LEFT JOIN (
    SELECT CONCAT(requests.tanggal_permintaan, '#' , requests.status) AS 'id', requests.tanggal_permintaan, requests.status, requests.total 
    FROM (
        SELECT
            DATE(request.tanggal_permintaan) as tanggal_permintaan,
            request.`status`,
            COUNT(*) as total
        FROM request
        WHERE YEAR(request.tanggal_permintaan) = '2018'
        GROUP BY DATE(request.tanggal_permintaan), request.status
    ) requests
) AS transactions
ON transactions.id = standard.id

ORDER BY standard.id

MySQL - Join With Default Value

4 Answers4