Alright, pretty basic I guess.

[Anon]

I have two tables:

GAMES
id, title, coder_id

CODERS
id, name

The coders table is left joined from the games table, where coder_id is the id of a coder.

Problem is, there are way too many coders which are not used by any game and I want to delete all coders that didn't code any games. Could anyone suggest the SQL for this?

[siwis]

There's probably a really nice and easy JOIN query to do what you want, but I can't fathom it either, so a simple DB cleanup like the following should do the trick.

$coders_query = mysql_query("SELECT id FROM CODERS", $db);
while ($coder = mysql_fetch_array(coders_query)) {
$coder_id = $coder['id'];
$games_query = mysql_query(SELECT * FROM GAMES WHERE coder_id = $coder_id", $db);
$coded = mysql_num_rows($games_query);
if ($coded >= "1") {
echo "Coder Retained";
} else {
$delete_query = mysql_query("DELETE FROM CODERS WHERE id = $coder_id", $db);
};
};
echo "Inactive Coders Cleaned from database!";

[siwis]

oops, that should read:

while ($coder = mysql_fetch_array($coders_query)) {

[Anon]

This is MySQL I presume?

Well then use Si's advice. But really, you should declare a foreign key:

alter table coders add foreign key (id) references games(id);

But that generally won't work in MySQL, as it has crap foreign key support.

To clean up the mess (that is inevitable without foreign keys), you should use a subselect:

delete from coders c where not exists (select id from games where id = c.id);

However, I'm pretty sure MySQL doesn't support subselects yet. It might support the exists syntax tho.

Chris