help with LEFT JOIN / NOT IN query

bleach

I have 2 tables;

table: modules
------------------------------------------
|  module_id  |  module_name     |
------------------------------------------
|       1            |  Test Mod 1         |
|       2            |  Test Mod 2         |
|       3            |  Test Mod 3         |
|       4            |  Test Mod 4         |
|                     |                            |
------------------------------------------

table: stats
------------------------------------------
|  stat_id        |  module_id          |
------------------------------------------
|       1            |            1              |
|       2            |            2              |
|       3            |            3              |
|                     |                            |
|                     |                            |
------------------------------------------

I need a single query that will find what modules are NOT in the stats table and return the module_names of those modules.

I'm assuming the query should look something like this but this query doesn't work...

$sql = "SELECT module_name FROM modules LEFT JOIN stats ON modules.module_id = stats.module_id WHERE modules.module_id NOT IN stats.module_id";
$result = mysql_query($sql, $db) or die(mysql_error());

the error I get is;
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'stats.module_id' at line 1

Anyone know how to write this so it works? THe result should return "Test Mod 4"

planetsim

Pretty useless LEFT JOIN. Why not just use the one table, If you do not want get results from the other.

$sql = "SELECT module_name FROM modules LEFT JOIN stats ON modules.module_id = stats.module_id WHERE modules.module_id = '$module_id'";

Will probably work, assuming $module_id has the value

ahundiak

From the mysql manual
If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;