By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS tbl1
CREATE TABLE tbl1 (
name VARCHAR (50) NOT NULL,
nameid INT NOT NULL,
count INT NOT NULL,
date DATETIME
);
INSERT INTO tbl1(name, nameid, count, date)
VALUES ('apple', 1, 77, '2019-07-29'),
('orange', 2, 129, '2019-07-29'),
('apple', 1, 399, '2019-08-05'),
('orange', 2, 27, '2019-08-05'),
('apple', 1, 122, '2019-08-12'),
('orange', 2, 5, '2019-08-19');
DROP TABLE IF EXISTS tbl2
CREATE TABLE tbl2 (
timeid INT NOT NULL,
date DATETIME
);
INSERT INTO tbl2(timeid, date)
VALUES (5,'2019-07-29'),
(4,'2019-08-05'),
(3,'2019-08-12'),
(2,'2019-08-19'),
(1,'2019-08-26');
11 rows affected
SELECT t2.date, n.name, n.nameid, COALESCE(t1.count, 0) AS count, t2.timeid
FROM tbl2 t2
CROSS APPLY (SELECT DISTINCT name, nameid FROM tbl1) n
LEFT JOIN tbl1 t1 ON t1.date = t2.date AND t1.name = n.name
ORDER BY t2.date, n.name
| date | name | nameid | count | timeid |
|---|---|---|---|---|
| 2019-07-29 00:00:00.000 | apple | 1 | 77 | 5 |
| 2019-07-29 00:00:00.000 | orange | 2 | 129 | 5 |
| 2019-08-05 00:00:00.000 | apple | 1 | 399 | 4 |
| 2019-08-05 00:00:00.000 | orange | 2 | 27 | 4 |
| 2019-08-12 00:00:00.000 | apple | 1 | 122 | 3 |
| 2019-08-12 00:00:00.000 | orange | 2 | 0 | 3 |
| 2019-08-19 00:00:00.000 | apple | 1 | 0 | 2 |
| 2019-08-19 00:00:00.000 | orange | 2 | 5 | 2 |
| 2019-08-26 00:00:00.000 | apple | 1 | 0 | 1 |
| 2019-08-26 00:00:00.000 | orange | 2 | 0 | 1 |