MYSQL: Date between + join

[red_ruewei]

Hi guys,
Need your help/advice/opinion for below sql.
I try to select all count record that exist in tables between to date insert by user.Currently, just put dummy date to test. The result is ok, but it only view for match data. How to include the record that not match as long is between those two date.
Suppose the record will show all record from month January to December but is only show starting with April which it only record that exist in db.I'm thinking to use JOIN to fix this, but not work.Thanks in advance.

SELECT DISTINCT DATE_FORMAT(a.tarikh_terima,'%M')AS bln,

    COUNT(CASE WHEN a.ID_KATEGORI=1 THEN 1 ELSE NULL END) AS aduan,
    COUNT(CASE WHEN a.ID_KATEGORI=2 THEN 1 ELSE NULL END) AS komen,
    COUNT(CASE WHEN a.ID_KATEGORI=3 THEN 1 ELSE NULL END) AS pertanyaan,
    COUNT(CASE WHEN a.ID_KATEGORI=4 THEN 1 ELSE NULL END) AS cadangan,
    COUNT(a.id_aduan) AS jumlah
    
    FROM s01_aduan a, lkp_bahagian b, s01_aduan_perkara c
    
    WHERE a.id_aduan=c.id_aduan
    AND c.id_bahagian=b.bahagian_id
    AND a.tarikh_terima BETWEEN STR_TO_DATE('01-01-2010', '%d-%m-%Y') AND   STR_TO_DATE('31-12-2010', '%d-%m-%Y')
    AND b.nama_bahagian='Bahagian Konsular'
    GROUP BY DATE_FORMAT(a.tarikh_terima,'%M')ASC
    ORDER BY DATE_FORMAT(a.tarikh_terima,'%M') ASC;

[tesuji]

Selamat Siang,

what you are doing is conventional inner join where join conditions ar in where-clause. The result set of inner join only contains rows where these join conditions (a.id_aduan=c.id_aduan AND c.id_bahagian=b.bahagian_id) be completely matched. Only outer joins (left, right, full) guarantee that rows are included in result set where join conditions are not completely matched. This doesn't affect the other predicates, e.g. AND b.nama_bahagian='Bahagian Konsular'.

Btw, instead of

GROUP BY DATE_FORMAT(a.tarikh_terima,'%M')ASC

you can also write

GROUP BY bln

You should omit ASC in GROUP-BY because this clause does not have such thing.

Well, you order the results alphabetically by name of month, so April, August, December ... will be the order, more common seems to be ordering by number of month.

-- tesu

[red_ruewei]

Hi tesuji, thanks for reply.

I try to use outer join.but not working.i google a bit.
and try do like this by using LEFT OUTER JOIN. the result is still same but only is take a bit time to query
Actually below code is only portion

SELECT DISTINCT DATE_FORMAT(a.tarikh_terima,'%M')AS bln,
   

    COUNT(CASE WHEN a.ID_KATEGORI=1 THEN 1 ELSE NULL END) AS aduan,
    COUNT(CASE WHEN a.ID_KATEGORI=2 THEN 1 ELSE NULL END) AS komen,
    COUNT(CASE WHEN a.ID_KATEGORI=3 THEN 1 ELSE NULL END) AS pertanyaan,
    COUNT(CASE WHEN a.ID_KATEGORI=4 THEN 1 ELSE NULL END) AS cadangan,
    COUNT(a.id_aduan) AS jumlah
    
    FROM s01_aduan a LEFT OUTER JOIN lkp_bahagian b, s01_aduan_perkara c
    ON
    a.id_aduan=c.id_aduan
    AND c.id_bahagian=b.bahagian_id
    AND a.tarikh_terima BETWEEN STR_TO_DATE('01-01-2010', '%d-%m-%Y') AND STR_TO_DATE('31-12-2010', '%d-%m-%Y')
    AND b.nama_bahagian='Bahagian Konsular'
    GROUP BY DATE_FORMAT(a.tarikh_terima,'%M')
    ORDER BY  bln

the original code like below

SELECT  UPPER(CONCAT( DATE_FORMAT(CURDATE(),'%d %M %Y'),' ',TIME_FORMAT(CURTIME(),'%h:%i %p'))) AS DATE,
IFNULL(a.bln,0) AS 'Bulan', IFNULL(b.aduan,0) AS 'Aduan', IFNULL(b.komen,0) AS 'Komen',IFNULL(b.pertanyaan,0) AS 'Pertanyaan',
IFNULL(b.cadangan,0) AS 'Cadangan', IFNULL(b.jumlah,0) AS 'Jumlah'
FROM
(
SELECT '02' kodbln, 'Januari' bln FROM DUAL WHERE '01' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '02' kodbln, 'Februari' bln FROM DUAL WHERE '02' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '03' kodbln, 'Mac' bln FROM DUAL WHERE '03' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '04' kodbln, 'April' bln FROM DUAL WHERE '04' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '05' kodbln, 'Mei' bln FROM DUAL WHERE '05' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '06' kodbln, 'Jun' bln FROM DUAL WHERE '06' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '07' kodbln, 'Julai' bln FROM DUAL WHERE '07' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '08' kodbln, 'Ogos' bln FROM DUAL WHERE '08' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '09' kodbln, 'September' bln FROM DUAL WHERE '09' AND DATE_FORMAT(STR_TO_DATE( '2010','%Y'),'%Y')
UNION
SELECT '10' kodbln, 'Oktober' bln FROM DUAL WHERE '10' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '11' kodbln, 'November' bln FROM DUAL WHERE '11' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')
UNION
SELECT '12' kodbln, 'Disember' bln FROM DUAL WHERE '12' AND DATE_FORMAT(STR_TO_DATE('2010','%Y'),'%Y')

) a LEFT OUTER JOIN
(
    SELECT DISTINCT DATE_FORMAT(a.tarikh_terima,'%M')AS bln,

    COUNT(CASE WHEN a.ID_KATEGORI=1 THEN 1 ELSE NULL END) AS aduan,
    COUNT(CASE WHEN a.ID_KATEGORI=2 THEN 1 ELSE NULL END) AS komen,
    COUNT(CASE WHEN a.ID_KATEGORI=3 THEN 1 ELSE NULL END) AS pertanyaan,
    COUNT(CASE WHEN a.ID_KATEGORI=4 THEN 1 ELSE NULL END) AS cadangan,
    COUNT(a.id_aduan) AS jumlah
    FROM KLNDB.S01_ADUAN a, personel.lkp_bahagian b, KLNDB.S01_ADUAN_PERKARA c
    WHERE a.id_aduan=c.id_aduan
    AND c.id_bahagian=b.bahagian_id
    AND a.tarikh_terima BETWEEN STR_TO_DATE('01-04-2010', '%d-%m-%Y') AND STR_TO_DATE('31-06-2010', '%d-%m-%Y')
    AND b.nama_bahagian='Bahagian Konsular'
    GROUP BY DATE_FORMAT(a.tarikh_terima,'%M')ASC
    ORDER BY DATE_FORMAT(a.tarikh_terima,'%M') ASC

) b
ON a.bln = b.bln

my intention to list all month from 01-01-2010 to 31-12-2010
which is record will be like this

aduan komen pertanyaan cadangan jumlah
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
January 0 0 0 0 0
February 68 0 3265 136 3469
March 1 1 1 1 1
April 34 4 5 0 23
June 0 0 0 0 0
July
.
.
.
December

thanks in advance

[tesuji]

hi

The from clause of your first select has 3 joins, no matter whether they are inner or outer joins. Also the dates should be correctly formatted and the order-by predicate could be changed. Therefore the correct wording seems to be:

...
  FROM s01_aduan a 
    OUTER JOIN lkp_bahagian b ON a.id_aduan = b.bahagian_id
    OUTER JOIN s01_aduan_perkara c ON b.bahagian_id = c.id_aduan
  WHERE STR_TO_DATE(a.tarikh_terima, '%d-%m-%Y') BETWEEN STR_TO_DATE('01-01-2010', '%d-%m-%Y') 
    AND STR_TO_DATE('31-12-2010', '%d-%m-%Y') AND b.nama_bahagian='Bahagian Konsular' 
  GROUP BY bln
  ORDER by a.tarikh_terima  -- if tarikh_terima is of date-type, the order will be Jan, Feb, Mar ... Dec

It depends on you which sort of joins to be applied.

I think I can give more help if I know the create-table statements of all related tables, especially of tables s01_aduan, lkp_bahagian, s01_aduan_perkar. Your 2nd select has some more, e.g. table DUAL.

For choosing the right joins it is also important that all primary and forein keys of these tables be identified.

Can you post all these pieces of information?

-- tesu

[red_ruewei]

thanks tesu...
here the related table

[tesuji]

Helo

I think, now I have got a fair impression of what you are intended to do. I would suggest that you test the following query.

select monthname(tarikh_terima) as "Bulan", id_kategori as "Kategori", count(Kategori) as "Bilangan" from S01_ADUAN GROUP BY Bulan, Kategori ORDER BY tarikh_terima;

The idea of this query is that you pre-select your data and in 2nd step this data will be further arranged to meet your spread-sheet-like chart. Tell me its result and whether it meets your concept to some degree, especially whether you are missing some pieces of necessary information.

I would like to know what data is in table Kategoi (there must be such a table because id_kategori seems to be foreign key in S01_ADUAN). Please, state some data examples, say 4 to 5 rows.

Attached is an ERM of your three tables generated by reengineering tool where I decided that all attributes consisting of id + table name are primary keys (red color) and all other attributes having id in their names are foreign keys (green colors), there aren't identifying relationships to the extent I see. In case of foreign keys the unknown reference tables where formally created with names derived from foreign keys. This decision was necessary because, unfortunately, there aren't any primary or foreign key constraints defined in your tables. You may correct this ERM.

Well, is your database schema strictly prespecified or are there any changes possible (e.g. isam -> innodb, adding primary + foreign keys, changing DT of keys from double into int etc)?

-- tesu