Issue with mysql query

Question

I have the following query

SELECT 
  `c`.`id` AS `confession_id`,
  `c`.`account_id` AS `confession_author_id`,
  `c`.`confession_text`,
  `c`.`anonymous`,
  `c`.`total_likes`,
  `c`.`total_comments`,
  `c`.`creation_date`,
  `a`.`username`,
  `col`.`name` AS `college`,
  `col`.`avatar_url`,
  `cll`.`deleted` AS `tacc_exists` 
FROM
  `confessions` AS `c` 
  LEFT JOIN `accounts` AS `a` 
    ON `a`.`id` = `c`.`account_id` 
  LEFT JOIN `colleges` AS `col` 
    ON `col`.`id` = `c`.`college_id` 
  LEFT JOIN `confession_likes_log` AS `cll` 
    ON `cll`.`confession_id` = `c`.`id` 
    AND `cll`.`account_id` = :taccd_user 
    AND `c`.`college_id` IN ('9') 
ORDER BY `c`.`id` DESC 
LIMIT 10 

It's suppose to select entries from specified colleges based on the college id(s) supplied

AND `c`.`college_id` in ('9')

Although for some reason it is selecting entries even when the college_id is not the one specified. I was having troubles figuring out how to do the and clause with the left joins so I may have not formatted the query correctly.

Answer 1

This is your where clause:

              FROM `confessions` AS `c` LEFT JOIN
                   `colleges` AS `col`
                    ON `col`.`id` = `c`.`college_id` LEFT JOIN
                   `confession_likes_log` AS `cll`
                    ON `cll`.`confession_id` = `c`.`id` AND
                       `cll`.`account_id` = :taccd_user AND
                        `c`.`college_id` in ('9')

The way a left join works is: Run the test in the on clause. If there is no match for a given row in the first table, then include the row with NULL values from the right table. Guess what. This even applies to conditions on the first table.

So, the filter c.college_id in ('9') isn't doing what you think. You need to move it to awhere` clause:

              FROM `confessions` AS `c` LEFT JOIN
                   `colleges` AS `col`
                    ON `col`.`id` = `c`.`college_id` LEFT JOIN
                   `confession_likes_log` AS `cll`
                    ON `cll`.`confession_id` = `c`.`id` AND
                       `cll`.`account_id` = :taccd_user
              WHERE `c`.`college_id` in ('9')