Fetch all users' download details

1
votes

My Table Structure ---

--
-- Database: `sample`
--
CREATE DATABASE IF NOT EXISTS `sample` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `sample`;

-- --------------------------------------------------------

--
-- Table structure for table `downloads`
--

DROP TABLE IF EXISTS `downloads`;
CREATE TABLE IF NOT EXISTS `downloads` (
  `dload_id` int(11) NOT NULL AUTO_INCREMENT,
  `wall_id` int(11) NOT NULL,
  `dload_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dload_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;


--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `folder_id` varchar(45) DEFAULT NULL,
  `last_login` varchar(45) DEFAULT NULL,
  `status` set('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `folder_id_UNIQUE` (`folder_id`),
  UNIQUE KEY `login_UNIQUE` (`login`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wallpapers`
--

DROP TABLE IF EXISTS `wallpapers`;
CREATE TABLE IF NOT EXISTS `wallpapers` (
  `wall_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `filename` varchar(100) DEFAULT NULL,
  `upload_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` set('0','1') DEFAULT '',
  PRIMARY KEY (`wall_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
  • 'users' contain users' login details etc.
  • 'wallpapers' contain wallpaper uploaded by user and
  • 'download' contains wallpapers downloaded by online users.

SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloads

FROM downloads AS D

LEFT JOIN wallpapers AS W ON W.wall_id = D.wall_id

LEFT JOIN users AS U on W.user_id = U.user_id

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY U.user_id, filename

ORDER BY user_id ASC LIMIT 500

above query just returns data for users who's wallpapers have been downloaded but I need to list down all users in my user table whether their wallpapers have been downloaded or not.

Above query's result

Above query's result

-

Expected Result

Expected Result

Thank you

Sample Data

INSERT INTO `downloads` (`dload_id`, `wall_id`, `dload_date`) VALUES
(1, 1, '2020-01-01 00:00:00'),
(2, 7, '2020-01-01 00:00:00'),
(3, 7, '2020-01-01 00:00:00'),
(4, 7, '2020-01-01 00:00:00'),
(5, 6, '2020-01-02 00:00:00'),
(6, 7, '2020-01-02 00:00:00'),
(7, 6, '2020-01-02 00:00:00'),
(8, 6, '2020-01-02 00:00:00'),
(9, 5, '2020-01-02 00:00:00'),
(10, 5, '2020-01-03 00:00:00'),
(11, 5, '2020-01-03 00:00:00'),
(12, 3, '2020-01-03 00:00:00'),
(13, 2, '2020-01-04 00:00:00'),
(14, 1, '2020-01-04 00:00:00'),
(15, 5, '2020-01-04 00:00:00'),
(16, 5, '2020-01-04 00:00:00'),
(17, 3, '2020-01-05 00:00:00'),
(18, 1, '2020-01-06 00:00:00'),
(19, 1, '2020-01-06 00:00:00'),
(20, 6, '2020-01-07 00:00:00'),
(21, 6, '2020-01-08 00:00:00'),
(22, 5, '2020-01-10 00:00:00'),
(23, 5, '2020-01-11 00:00:00'),
(24, 5, '2020-01-11 00:00:00'),
(25, 3, '2020-01-13 00:00:00'),
(26, 2, '2020-01-13 00:00:00'),
(27, 7, '2020-01-15 00:00:00'),
(28, 2, '2020-01-16 00:00:00'),
(29, 3, '2020-01-16 00:00:00'),
(30, 4, '2020-01-16 00:00:00'),
(31, 7, '2020-01-18 00:00:00'),
(32, 7, '2020-01-18 00:00:00'),
(33, 7, '2020-01-20 00:00:00'),
(34, 6, '2020-01-21 00:00:00'),
(35, 7, '2020-01-21 00:00:00'),
(36, 6, '2020-01-21 00:00:00'),
(37, 6, '2020-01-22 00:00:00'),
(38, 5, '2020-01-23 00:00:00'),
(39, 5, '2020-01-24 00:00:00'),
(40, 2, '2020-01-25 00:00:00'),
(41, 3, '2020-01-25 00:00:00'),
(42, 4, '2020-01-26 00:00:00'),
(43, 1, '2020-01-26 00:00:00'),
(44, 2, '2020-01-26 00:00:00'),
(45, 3, '2020-01-28 00:00:00'),
(46, 7, '2020-01-28 00:00:00'),
(47, 7, '2020-01-29 00:00:00'),
(48, 7, '2020-01-29 00:00:00'),
(49, 6, '2020-01-29 00:00:00'),
(50, 7, '2020-01-29 00:00:00'),
(51, 6, '2020-01-29 00:00:00'),
(52, 6, '2020-01-29 00:00:00'),
(53, 5, '2020-01-29 00:00:00'),
(54, 5, '2020-01-29 00:00:00'),
(55, 5, '2020-01-29 00:00:00'),
(56, 3, '2020-02-01 00:00:00'),
(57, 2, '2020-02-01 00:00:00'),
(58, 1, '2020-02-01 00:00:00'),
(59, 5, '2020-02-01 00:00:00'),
(60, 3, '2020-02-02 00:00:00'),
(61, 2, '2020-02-02 00:00:00'),
(62, 1, '2020-02-02 00:00:00'),
(63, 2, '2020-02-02 00:00:00'),
(64, 3, '2020-02-02 00:00:00'),
(65, 4, '2020-02-03 00:00:00'),
(66, 2, '2020-02-03 00:00:00'),
(67, 2, '2020-02-03 00:00:00'),
(68, 5, '2020-02-05 00:00:00'),
(69, 5, '2020-02-05 00:00:00'),
(70, 5, '2020-02-05 00:00:00'),
(71, 5, '2020-02-06 00:00:00'),
(72, 7, '2020-02-06 00:00:00'),
(73, 7, '2020-02-07 00:00:00'),
(74, 7, '2020-02-08 00:00:00'),
(75, 6, '2020-02-09 00:00:00'),
(76, 7, '2020-02-09 00:00:00'),
(77, 6, '2020-02-12 00:00:00'),
(78, 6, '2020-02-12 00:00:00'),
(79, 5, '2020-02-12 00:00:00'),
(80, 5, '2020-02-16 00:00:00'),
(81, 5, '2020-02-16 00:00:00'),
(82, 3, '2020-02-18 00:00:00'),
(83, 2, '2020-02-18 00:00:00'),
(84, 1, '2020-02-20 00:00:00'),
(85, 2, '2020-02-20 00:00:00'),
(86, 3, '2020-02-21 00:00:00'),
(87, 4, '2020-02-21 00:00:00');


INSERT INTO `users` (`user_id`, `login`, `password`, `folder_id`, `last_login`, `status`) VALUES
(1, '[email protected]', '1111', 'A001', NULL, '1'),
(2, '[email protected]', '1111', 'A002', NULL, '1'),
(3, '[email protected]', '1111', 'A003', NULL, '1'),
(4, '[email protected]', '1111', 'A004', NULL, '1'),
(5, '[email protected]', '1111', 'A005', NULL, '1'),
(6, '[email protected]', '1111', 'A006', NULL, '1'),
(7, '[email protected]', '1111', 'A007', NULL, '1'),
(8, '[email protected]', '1111', 'A008', NULL, '1'),
(9, '[email protected]', '1111', 'A009', NULL, '1'),
(10, '[email protected]', '1111', 'A010', NULL, '1');


INSERT INTO `wallpapers` (`wall_id`, `user_id`, `filename`, `upload_date`, `status`) VALUES
(1, 2, 'wallpaper1.jpg', '2020-02-01 00:00:00', '1'),
(2, 1, 'wallpaper2.jpg', '2020-02-01 00:00:00', '1'),
(3, 1, 'wallpaper3.jpg', '2020-02-01 00:00:00', '1'),
(4, 2, 'wallpaper4.jpg', '2020-02-01 00:00:00', '1'),
(5, 3, 'wallpaper5.jpg', '2020-02-01 00:00:00', '1'),
(6, 5, 'wallpaper6.jpg', '2020-02-08 00:00:00', '1'),
(7, 6, 'wallpaper7.jpg', '2020-02-08 00:00:00', '1'),
(8, 5, 'wallpaper8.jpg', '2020-02-08 00:00:00', '1'),
(9, 6, 'wallpaper9.jpg', '2020-02-08 00:00:00', '1'),
(10, 4, 'wallpaper10.jpg', '2020-02-08 00:00:00', '1'),
(11, 6, 'wallpaper11.jpg', '2020-02-09 00:00:00', '1'),
(12, 5, 'wallpaper12.jpg', '2020-02-09 00:00:00', '1'),
(13, 1, 'wallpaper13.jpg', '2020-02-09 00:00:00', '1'),
(14, 4, 'wallpaper14.jpg', '2020-02-10 00:00:00', '1'),
(15, 6, 'wallpaper15.jpg', '2020-02-10 00:00:00', '1'),
(16, 1, 'wallpaper16.jpg', '2020-02-10 00:00:00', '1'),
(17, 2, 'wallpaper17.jpg', '2020-02-13 00:00:00', '1'),
(18, 4, 'wallpaper18.jpg', '2020-02-13 00:00:00', '1'),
(19, 6, 'wallpaper19.jpg', '2020-02-19 00:00:00', '1'),
(20, 1, 'wallpaper20.jpg', '2020-02-19 00:00:00', '1');
4
phpmysql

4 Answers

1
votes

Try with this query

SELECT U.user_id, U.login,N.dload_id,N.filename,N.total_downloads 
FROM `users` U left join 
( 
   SELECT D.wall_id,W.filename,W.user_id,D.dload_id,count(D.wall_id) as total_downloads 
   FROM `downloads` D 
   left join wallpapers W on D.wall_id=W.wall_id 
   where D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00' 
   GROUP BY D.`wall_id`
) N on U.user_id=N.user_id

Here you need to use subquery. Try this query, hopefully it will be work fine.

1
votes

You have wrong order in your query. Since you start with downloads you already filter out users without download regardless you put left join. If you start with users it should be ok.

SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloads

FROM users AS U

LEFT JOIN wallpapers AS W on W.user_id = U.user_id

LEFT JOIN downloads AS D ON W.wall_id = D.wall_id

WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

GROUP BY U.user_id, filename

ORDER BY user_id ASC LIMIT 500
1
votes

You can use this Query

It is using a subquery to count he numbers of a wall_id

SELECT 
U.`user_id`
, U.login
,W.filename
 ,D.total_counts
FROM wallpapers AS W LEFT Join (
SELECT Count(*) total_counts, wall_id FROM downloads AS D
WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'
GROUP BY wall_id) D ON D.wall_id = W.wall_id RIGHT JOIN users AS U on W.user_id = U.user_id
ORDER BY U.user_id,W.wall_id;

-- Table structure for table `downloads`
--

DROP TABLE IF EXISTS `downloads`;
CREATE TABLE IF NOT EXISTS `downloads` (
  `dload_id` int(11) NOT NULL AUTO_INCREMENT,
  `wall_id` int(11) NOT NULL,
  `dload_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dload_id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;

✓

✓

-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `folder_id` varchar(45) DEFAULT NULL,
  `last_login` varchar(45) DEFAULT NULL,
  `status` set('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `folder_id_UNIQUE` (`folder_id`),
  UNIQUE KEY `login_UNIQUE` (`login`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

✓

✓

--

--
-- Table structure for table `wallpapers`
--

DROP TABLE IF EXISTS `wallpapers`;
CREATE TABLE IF NOT EXISTS `wallpapers` (
  `wall_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `filename` varchar(100) DEFAULT NULL,
  `upload_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` set('0','1') DEFAULT '',
  PRIMARY KEY (`wall_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

✓

✓

INSERT INTO `downloads` (`dload_id`, `wall_id`, `dload_date`) VALUES
(1, 1, '2020-01-01 00:00:00'),
(2, 7, '2020-01-01 00:00:00'),
(3, 7, '2020-01-01 00:00:00'),
(4, 7, '2020-01-01 00:00:00'),
(5, 6, '2020-01-02 00:00:00'),
(6, 7, '2020-01-02 00:00:00'),
(7, 6, '2020-01-02 00:00:00'),
(8, 6, '2020-01-02 00:00:00'),
(9, 5, '2020-01-02 00:00:00'),
(10, 5, '2020-01-03 00:00:00'),
(11, 5, '2020-01-03 00:00:00'),
(12, 3, '2020-01-03 00:00:00'),
(13, 2, '2020-01-04 00:00:00'),
(14, 1, '2020-01-04 00:00:00'),
(15, 5, '2020-01-04 00:00:00'),
(16, 5, '2020-01-04 00:00:00'),
(17, 3, '2020-01-05 00:00:00'),
(18, 1, '2020-01-06 00:00:00'),
(19, 1, '2020-01-06 00:00:00'),
(20, 6, '2020-01-07 00:00:00'),
(21, 6, '2020-01-08 00:00:00'),
(22, 5, '2020-01-10 00:00:00'),
(23, 5, '2020-01-11 00:00:00'),
(24, 5, '2020-01-11 00:00:00'),
(25, 3, '2020-01-13 00:00:00'),
(26, 2, '2020-01-13 00:00:00'),
(27, 7, '2020-01-15 00:00:00'),
(28, 2, '2020-01-16 00:00:00'),
(29, 3, '2020-01-16 00:00:00'),
(30, 4, '2020-01-16 00:00:00'),
(31, 7, '2020-01-18 00:00:00'),
(32, 7, '2020-01-18 00:00:00'),
(33, 7, '2020-01-20 00:00:00'),
(34, 6, '2020-01-21 00:00:00'),
(35, 7, '2020-01-21 00:00:00'),
(36, 6, '2020-01-21 00:00:00'),
(37, 6, '2020-01-22 00:00:00'),
(38, 5, '2020-01-23 00:00:00'),
(39, 5, '2020-01-24 00:00:00'),
(40, 2, '2020-01-25 00:00:00'),
(41, 3, '2020-01-25 00:00:00'),
(42, 4, '2020-01-26 00:00:00'),
(43, 1, '2020-01-26 00:00:00'),
(44, 2, '2020-01-26 00:00:00'),
(45, 3, '2020-01-28 00:00:00'),
(46, 7, '2020-01-28 00:00:00'),
(47, 7, '2020-01-29 00:00:00'),
(48, 7, '2020-01-29 00:00:00'),
(49, 6, '2020-01-29 00:00:00'),
(50, 7, '2020-01-29 00:00:00'),
(51, 6, '2020-01-29 00:00:00'),
(52, 6, '2020-01-29 00:00:00'),
(53, 5, '2020-01-29 00:00:00'),
(54, 5, '2020-01-29 00:00:00'),
(55, 5, '2020-01-29 00:00:00'),
(56, 3, '2020-02-01 00:00:00'),
(57, 2, '2020-02-01 00:00:00'),
(58, 1, '2020-02-01 00:00:00'),
(59, 5, '2020-02-01 00:00:00'),
(60, 3, '2020-02-02 00:00:00'),
(61, 2, '2020-02-02 00:00:00'),
(62, 1, '2020-02-02 00:00:00'),
(63, 2, '2020-02-02 00:00:00'),
(64, 3, '2020-02-02 00:00:00'),
(65, 4, '2020-02-03 00:00:00'),
(66, 2, '2020-02-03 00:00:00'),
(67, 2, '2020-02-03 00:00:00'),
(68, 5, '2020-02-05 00:00:00'),
(69, 5, '2020-02-05 00:00:00'),
(70, 5, '2020-02-05 00:00:00'),
(71, 5, '2020-02-06 00:00:00'),
(72, 7, '2020-02-06 00:00:00'),
(73, 7, '2020-02-07 00:00:00'),
(74, 7, '2020-02-08 00:00:00'),
(75, 6, '2020-02-09 00:00:00'),
(76, 7, '2020-02-09 00:00:00'),
(77, 6, '2020-02-12 00:00:00'),
(78, 6, '2020-02-12 00:00:00'),
(79, 5, '2020-02-12 00:00:00'),
(80, 5, '2020-02-16 00:00:00'),
(81, 5, '2020-02-16 00:00:00'),
(82, 3, '2020-02-18 00:00:00'),
(83, 2, '2020-02-18 00:00:00'),
(84, 1, '2020-02-20 00:00:00'),
(85, 2, '2020-02-20 00:00:00'),
(86, 3, '2020-02-21 00:00:00'),
(87, 4, '2020-02-21 00:00:00');


INSERT INTO `users` (`user_id`, `login`, `password`, `folder_id`, `last_login`, `status`) VALUES
(1, '[email protected]', '1111', 'A001', NULL, '1'),
(2, '[email protected]', '1111', 'A002', NULL, '1'),
(3, '[email protected]', '1111', 'A003', NULL, '1'),
(4, '[email protected]', '1111', 'A004', NULL, '1'),
(5, '[email protected]', '1111', 'A005', NULL, '1'),
(6, '[email protected]', '1111', 'A006', NULL, '1'),
(7, '[email protected]', '1111', 'A007', NULL, '1'),
(8, '[email protected]', '1111', 'A008', NULL, '1'),
(9, '[email protected]', '1111', 'A009', NULL, '1'),
(10, '[email protected]', '1111', 'A010', NULL, '1');


INSERT INTO `wallpapers` (`wall_id`, `user_id`, `filename`, `upload_date`, `status`) VALUES
(1, 2, 'wallpaper1.jpg', '2020-02-01 00:00:00', '1'),
(2, 1, 'wallpaper2.jpg', '2020-02-01 00:00:00', '1'),
(3, 1, 'wallpaper3.jpg', '2020-02-01 00:00:00', '1'),
(4, 2, 'wallpaper4.jpg', '2020-02-01 00:00:00', '1'),
(5, 3, 'wallpaper5.jpg', '2020-02-01 00:00:00', '1'),
(6, 5, 'wallpaper6.jpg', '2020-02-08 00:00:00', '1'),
(7, 6, 'wallpaper7.jpg', '2020-02-08 00:00:00', '1'),
(8, 5, 'wallpaper8.jpg', '2020-02-08 00:00:00', '1'),
(9, 6, 'wallpaper9.jpg', '2020-02-08 00:00:00', '1'),
(10, 4, 'wallpaper10.jpg', '2020-02-08 00:00:00', '1'),
(11, 6, 'wallpaper11.jpg', '2020-02-09 00:00:00', '1'),
(12, 5, 'wallpaper12.jpg', '2020-02-09 00:00:00', '1'),
(13, 1, 'wallpaper13.jpg', '2020-02-09 00:00:00', '1'),
(14, 4, 'wallpaper14.jpg', '2020-02-10 00:00:00', '1'),
(15, 6, 'wallpaper15.jpg', '2020-02-10 00:00:00', '1'),
(16, 1, 'wallpaper16.jpg', '2020-02-10 00:00:00', '1'),
(17, 2, 'wallpaper17.jpg', '2020-02-13 00:00:00', '1'),
(18, 4, 'wallpaper18.jpg', '2020-02-13 00:00:00', '1'),
(19, 6, 'wallpaper19.jpg', '2020-02-19 00:00:00', '1'),
(20, 1, 'wallpaper20.jpg', '2020-02-19 00:00:00', '1');

✓

✓

✓

SELECT 
U.`user_id`
, U.login
,W.filename
 ,D.total_counts
FROM wallpapers AS W LEFT Join (
SELECT Count(*) total_counts, wall_id FROM downloads AS D
WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'
GROUP BY wall_id) D ON D.wall_id = W.wall_id RIGHT JOIN users AS U on W.user_id = U.user_id
ORDER BY U.user_id,W.wall_id;

user_id | login              | filename        | total_counts
------: | :----------------- | :-------------- | -----------:
      1 | [email protected]  | wallpaper2.jpg  |            5
      1 | [email protected]  | wallpaper3.jpg  |            6
      1 | [email protected]  | wallpaper13.jpg |         null
      1 | [email protected]  | wallpaper16.jpg |         null
      1 | [email protected]  | wallpaper20.jpg |         null
      2 | [email protected]  | wallpaper1.jpg  |            5
      2 | [email protected]  | wallpaper4.jpg  |            2
      2 | [email protected]  | wallpaper17.jpg |         null
      3 | [email protected]  | wallpaper5.jpg  |           13
      4 | [email protected]  | wallpaper10.jpg |         null
      4 | [email protected]  | wallpaper14.jpg |         null
      4 | [email protected]  | wallpaper18.jpg |         null
      5 | [email protected]  | wallpaper6.jpg  |           11
      5 | [email protected]  | wallpaper8.jpg  |         null
      5 | [email protected]  | wallpaper12.jpg |         null
      6 | [email protected]  | wallpaper7.jpg  |           13
      6 | [email protected]  | wallpaper9.jpg  |         null
      6 | [email protected]  | wallpaper11.jpg |         null
      6 | [email protected]  | wallpaper15.jpg |         null
      6 | [email protected]  | wallpaper19.jpg |         null
      7 | [email protected]  | null            |         null
      8 | [email protected]  | null            |         null
      9 | [email protected]  | null            |         null
     10 | [email protected] | null            |         null

    SELECT U.user_id, U.login, D.dload_id, W.filename, IFNULL(COUNT(D.dload_id), 0) AS total_downloads

    FROM downloads AS D

    LEFT JOIN wallpapers AS W ON W.wall_id = D.wall_id

    LEFT JOIN users AS U on W.user_id = U.user_id

    WHERE D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'

    GROUP BY U.user_id, U.login, D.dload_id, W.filename

    ORDER BY user_id ASC LIMIT 500

user_id | login             | dload_id | filename       | total_downloads
------: | :---------------- | -------: | :------------- | --------------:
      1 | [email protected] |       12 | wallpaper3.jpg |               1
      1 | [email protected] |       13 | wallpaper2.jpg |               1
      1 | [email protected] |       17 | wallpaper3.jpg |               1
      1 | [email protected] |       25 | wallpaper3.jpg |               1
      1 | [email protected] |       26 | wallpaper2.jpg |               1
      1 | [email protected] |       28 | wallpaper2.jpg |               1
      1 | [email protected] |       29 | wallpaper3.jpg |               1
      1 | [email protected] |       40 | wallpaper2.jpg |               1
      1 | [email protected] |       41 | wallpaper3.jpg |               1
      1 | [email protected] |       44 | wallpaper2.jpg |               1
      1 | [email protected] |       45 | wallpaper3.jpg |               1
      2 | [email protected] |        1 | wallpaper1.jpg |               1
      2 | [email protected] |       14 | wallpaper1.jpg |               1
      2 | [email protected] |       18 | wallpaper1.jpg |               1
      2 | [email protected] |       19 | wallpaper1.jpg |               1
      2 | [email protected] |       30 | wallpaper4.jpg |               1
      2 | [email protected] |       42 | wallpaper4.jpg |               1
      2 | [email protected] |       43 | wallpaper1.jpg |               1
      3 | [email protected] |        9 | wallpaper5.jpg |               1
      3 | [email protected] |       10 | wallpaper5.jpg |               1
      3 | [email protected] |       11 | wallpaper5.jpg |               1
      3 | [email protected] |       15 | wallpaper5.jpg |               1
      3 | [email protected] |       16 | wallpaper5.jpg |               1
      3 | [email protected] |       22 | wallpaper5.jpg |               1
      3 | [email protected] |       23 | wallpaper5.jpg |               1
      3 | [email protected] |       24 | wallpaper5.jpg |               1
      3 | [email protected] |       38 | wallpaper5.jpg |               1
      3 | [email protected] |       39 | wallpaper5.jpg |               1
      3 | [email protected] |       53 | wallpaper5.jpg |               1
      3 | [email protected] |       54 | wallpaper5.jpg |               1
      3 | [email protected] |       55 | wallpaper5.jpg |               1
      5 | [email protected] |        5 | wallpaper6.jpg |               1
      5 | [email protected] |        7 | wallpaper6.jpg |               1
      5 | [email protected] |        8 | wallpaper6.jpg |               1
      5 | [email protected] |       20 | wallpaper6.jpg |               1
      5 | [email protected] |       21 | wallpaper6.jpg |               1
      5 | [email protected] |       34 | wallpaper6.jpg |               1
      5 | [email protected] |       36 | wallpaper6.jpg |               1
      5 | [email protected] |       37 | wallpaper6.jpg |               1
      5 | [email protected] |       49 | wallpaper6.jpg |               1
      5 | [email protected] |       51 | wallpaper6.jpg |               1
      5 | [email protected] |       52 | wallpaper6.jpg |               1
      6 | [email protected] |        2 | wallpaper7.jpg |               1
      6 | [email protected] |        3 | wallpaper7.jpg |               1
      6 | [email protected] |        4 | wallpaper7.jpg |               1
      6 | [email protected] |        6 | wallpaper7.jpg |               1
      6 | [email protected] |       27 | wallpaper7.jpg |               1
      6 | [email protected] |       31 | wallpaper7.jpg |               1
      6 | [email protected] |       32 | wallpaper7.jpg |               1
      6 | [email protected] |       33 | wallpaper7.jpg |               1
      6 | [email protected] |       35 | wallpaper7.jpg |               1
      6 | [email protected] |       46 | wallpaper7.jpg |               1
      6 | [email protected] |       47 | wallpaper7.jpg |               1
      6 | [email protected] |       48 | wallpaper7.jpg |               1
      6 | [email protected] |       50 | wallpaper7.jpg |               1

db<>fiddle here

1
votes

If you want all the users in the results then you should start the join from Users and then left join the other tables.
Also the condition for the dates should be moved to the ON clause: 

SELECT U.user_id, U.login, W.filename, 
       COUNT(D.dload_id) AS total_downloads
FROM users AS U
LEFT JOIN wallpapers AS W ON W.user_id = U.user_id
LEFT JOIN downloads AS D ON W.wall_id = D.wall_id 
                        AND D.dload_date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 00:00:00'
GROUP BY U.user_id, U.login, W.filename
ORDER BY U.user_id ASC LIMIT 500

There is no need for IFNULL() because COUNT(D.dload_id) will return 0 if there are no downloads. See the demo.