add a new entry if doesn't exist in mysql table

Question

I have a table of below structure.

mysql> desc depot;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| recd  | date     | YES  |     | NULL    |       |
| id    | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

Currently I have records in the below manner.

mysql> select * from depot;
+---------------------+------+
| recd                | id   |
+---------------------+------+
| 2012-07-09          |   33 |
| 2012-07-11          |   32 |
| 2012-07-15          |   32 |
+---------------------+------+
3 rows in set (0.00 sec)

I need the records to print the query in the below manner, keeping the missed entries of dates of a month (say July-01 to July-31) and having 0 to the value id corresponding missed dates.

select < a magical query >;

+------------+------+
| recd       | id   |
+------------+------+
 2012-07-01 0
 2012-07-02 0
 2012-07-03 0
 2012-07-04 0
 2012-07-05 0
 2012-07-06 0
 2012-07-07 0
 2012-07-08 0
 2012-07-09 33
 2012-07-10 0
 2012-07-11 32
 2012-07-12 0
 2012-07-13 0
 2012-07-14 0
 2012-07-15 32
 2012-07-16 0
 2012-07-17 0
 2012-07-18 0
 2012-07-19 0
 2012-07-20 0
 2012-07-21 0
 2012-07-22 0
 2012-07-23 0
 2012-07-24 0
 2012-07-25 0
 2012-07-26 0
 2012-07-27 0
 2012-07-28 0
 2012-07-29 0
 2012-07-30 0
 2012-07-31 0

What DB engine are you using? – juergen d Aug 10 '12 at 10:51 — juergen d, Aug 10 '12 at 10:51

score 2 · Answer 1 · answered Aug 10 '12 at 09:40

2

You obviously need a second table with a list of possible dates and then you should select from that table with a left join to the one you already have.

answered Aug 10 '12 at 09:40

Alex

907
2
8
22

score 1 · Answer 2 · edited May 23 '17 at 12:27

A calendar table makes your query and your life easier. In standard SQL this query will give you what you're looking for.

select c.cal_date, coalesce(d.id, 0) id
from calendar c
left join depot d on d.recd = c.cal_date
where c.cal_date between '2012-07-01' and '2012-07-31'
order by c.cal_date

A minimal calendar table just needs a date column.

create table calendar (
  cal_date date primary key
);

insert into calendar values
('2012-07-01'),
('2012-07-02'),
...
('2012-07-31');

Instead of writing INSERT statements, you can generate data with a spreadsheet or a scripting program, and load the rows through your database's bulk loader.

I've also written about a more useful calendar table on StackOverflow.

score 0 · Answer 3 · answered Aug 10 '12 at 12:03

Thanks mates!! I was ambitious on any SQLs if existing. But yeah its reluctant procedure..

Found a workaround as it was clinging long time

BASE TABLE

 CREATE TABLE `deopt` (
  `recd` datetime DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB;
Seed records to the base table

insert into deopt values ('2012-07-09 23:08:54',22);
insert into deopt values ('2012-07-11 23:08:54',22);
insert into deopt values ('2012-07-11 23:08:54',2222);
insert into deopt values ('2012-07-12 23:08:54',22);
insert into deopt values ('2012-07-14 23:08:54',245);
Create a table for dates of a month

CREATE TABLE seq_dates 
(
   sdate DATETIME NOT NULL,

);
Create a Stored Procedure to create records for a called month

delimiter //
DROP PROCEDURE IF EXISTS sp_init_dates;

CREATE PROCEDURE sp_init_dates (IN p_fdate DATETIME, IN p_tdate DATETIME)
BEGIN 
DECLARE v_thedate DATETIME; 
TRUNCATE TABLE seq_dates; 
SET v_thedate = p_fdate;
WHILE (v_thedate <= p_tdate) DO 
   INSERT INTO seq_dates (sdate) 
   VALUES (v_thedate); 
   SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY); 
END WHILE; 
END;

delimiter ;
Call the procedure for July month with starting and ending values to be seeded to seq_dates table.

call sp_init_dates ('2012-07-01','2012-07-31'); 
RESULT QUERY - To fetch records of all dates in a month and its corresponding ids keeping 0 inplace of null for ids.

select date(seq_dates.sdate),coalesce (deopt.id,0) from seq_dates LEFT JOIN deopt ON date(deopt.recd)=date(seq_dates.sdate);



+-----------------------+-----------------------+
| date(seq_dates.sdate) | coalesce (deopt.id,0) |
+-----------------------+-----------------------+
| 2012-07-01            |                     0 |
| 2012-07-02            |                     0 |
| 2012-07-03            |                     0 |
| 2012-07-04            |                     0 |
| 2012-07-05            |                     0 |
| 2012-07-06            |                     0 |
| 2012-07-07            |                     0 |
| 2012-07-08            |                     0 |
| 2012-07-09            |                    22 |
| 2012-07-09            |                    22 |
| 2012-07-10            |                     0 |
| 2012-07-11            |                    22 |
| 2012-07-11            |                  2222 |
| 2012-07-11            |                    22 |
| 2012-07-11            |                  2222 |
| 2012-07-12            |                    22 |
| 2012-07-13            |                     0 |
| 2012-07-14            |                   245 |
| 2012-07-15            |                     0 |
| 2012-07-16            |                     0 |
| 2012-07-17            |                     0 |
| 2012-07-18            |                     0 |
| 2012-07-19            |                     0 |
| 2012-07-20            |                     0 |
| 2012-07-21            |                     0 |
| 2012-07-22            |                     0 |
| 2012-07-23            |                     0 |
| 2012-07-24            |                     0 |
| 2012-07-25            |                     0 |
| 2012-07-26            |                     0 |
| 2012-07-27            |                     0 |
| 2012-07-28            |                     0 |
| 2012-07-29            |                     0 |
| 2012-07-30            |                     0 |
| 2012-07-31            |                     0 |
+-----------------------+-----------------------+
35 rows in set (0.00 sec)

add a new entry if doesn't exist in mysql table

3 Answers3