Missing rows due to too strict WHERE statement

Question

In the example code below we have attempted to minimize our problem into a simplified test case.

We have 3 tables: users, images and ratings.

1: We want to print all entries from the ‘ratings’ table.

2: In the ‘images’ table, there can be several images uploaded by the same users into different sections of the site, e.g. ‘user_profile_picture’ or ‘user_cover_photo’.

3: When printing the ratings, we want to fetch entries from the ‘images’ table if the user has uploaded a profile picture (section = ‘profile_picture’).

The WHERE statement in the query in the example code fails for users with no profile pictures uploaded.

How can we make sure to print all entries, as well as only grab the image connected to the user if section = profile_picture …?

CREATE TABLE `test_ratings` (
  `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(4) UNSIGNED NOT NULL,
  `rating` enum('1','2','3','4','5') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
INSERT INTO `test_ratings` (`id`, `user_id`, `rating`) VALUES
    (2, 2, '4'),
    (1, 1, '5');
    
CREATE TABLE `test_users` (
    `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` varchar(30) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
INSERT INTO `test_users` (`id`, `username`) VALUES
    (1, 'Test person 1'),
    (2, 'Test person 2');
    
CREATE TABLE `test_users_images` (
    `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    `filename` varchar(80) NOT NULL,
    `section` enum('user_cover_photo','user_profile_picture') DEFAULT NULL,
    `user_id` int(4) UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
INSERT INTO `test_users_images` (`id`, `filename`, `section`, `user_id`) VALUES
    (1, 'rtfos.jpg', 'user_profile_picture', 2),
    (2, 'exer8.jpg', 'user_cover_photo', 2);
    
<?php
   $mysqli = new mysqli("localhost", "root", "", "website");

   $result = $mysqli->query('SELECT image.filename, user.username, ratings.rating
      FROM test_ratings ratings
      INNER JOIN test_users user ON user.id = ratings.user_id
      INNER JOIN test_users_images image ON image.user_id = user.id
      WHERE image.section = \'user_profile_picture\'');
  
   while ($row = $result->fetch_object()) {
      echo '<p>' . $row->username . ' - ' . $row->filename . '</p>';
   }
?>

Answer 1

As already commented, start with the user table and left join to it. Include the image section filter in the ON clause of the respective join.

SELECT image.filename,
       user.username,
       ratings.rating
       FROM test_users user
            LEFT JOIN test_users_images image
                      ON image.user_id = user.id
                         AND image.section = 'user_profile_picture'
            LEFT JOIN test_ratings ratings
                      ON user.id = ratings.user_id;

db<>fiddle

P.S.: It's a WHERE clause, not a statement.