MYSQL query with joins and WHERE condition not returning any rows

Question

I'm trying to run a SQL query that joins with 2 other tables. But, I need to still be able to use a WHERE condition in this query that uses the id of the main table. In this case the main table is timesheets.

I've tried the following but no rows were returned at all:

   SELECT * FROM `timesheets` t 
   INNER JOIN `users` u ON `t`.`teacher_id` = `u`.`id` 
   INNER JOIN `schools` s ON `t`.`school_id` = `s`.`id` 
   WHERE t.id = 46

In this example I'm trying to filter to the row with ID 46 which definitely does exist.

If I do a simple query of just the timesheets table with the same where condition of id = 46 it works and returns that specific row.

When using EXPLAIN I get this message in the 'extra' column:

Impossible WHERE noticed after reading const table

Any ideas what I'm doing wrong?

Answer 1

As you have used INNER JOIN , so it will return data only if tables users and schools primary key has been used in timesheet as a foreign key.

use left join

SELECT * FROM timesheets t 
   LEFT JOIN users u ON t.teacher_id = u.id 
   LEFT JOIN schools s ON t.school_id = s.id 
   WHERE t.id = 46