Page 1 of 1

How to check if an id exists in another table

Posted: Fri Oct 08, 2010 7:11 am
by Pav9495
I need php code to take the numbers from all rows of the column "id" from "wqw_users", then check whether these ids exist in the column "userid" from "wqw_items" and if they are other rows with different numbers to delete them. I already tried some things but it always deleted absolutely everything except the row with a userid 1 from "wqw_items", when there is an id 1 in "wqw_users" so it didn't delete it, but there are other ids too, so they also had to remain. Here is the code I used:

Code: Select all

$users = mysql_query ("SELECT * FROM wqw_users");
$all = mysql_fetch_assoc ($users);
$delete = mysql_query ("DELETE FROM wqw_items WHERE userid! =". $all ['id']."");
If anyone can help please respond. :)

Re: How to check if an id exists in another table

Posted: Fri Oct 08, 2010 8:15 am
by twinedev
Use this query:

Code: Select all

DELETE FROM `wqw_items` WHERE userid NOT IN (SELECT `id` FROM `wqw_users`);

Re: How to check if an id exists in another table

Posted: Fri Oct 08, 2010 2:36 pm
by John Cartwright
twinedev wrote:Use this query:

Code: Select all

DELETE FROM `wqw_items` WHERE userid NOT IN (SELECT `id` FROM `wqw_users`);
I wouldn't recommend using that query, as it will load the entire users table.

Instead, you can use a join to determine whether the id exists or not.

Code: Select all

DELETE FROM wqw_items AS item
LEFT JOIN wqw_users AS user ON item.userid = user.id
WHERE item.userid IS NULL