ybh305 — 2010-03-19T19:39:49-04:00 — #1
When I do SELECT COUNT(id) FROM customers it returns 1, 543. But When I JOIN the customers table with a 2nd table that references the IDs the number is actually higher.
Here is the query:
INNER JOIN favs
ON favs.userID = customers.id
So what I'm trying to do is show the IDs that are no longer in the customers table so that I can delete them and the COUNT can be accurate.
dan_grossman — 2010-03-19T19:49:11-04:00 — #2
If a user has 5 favs, there will be 5 rows for that user here. You are not counting distinct customers but how many customer-favorite pairs there are. That's why the number is higher.
An INNER JOIN won't ever return rows from one table that don't have corresponding rows in the other.
viflux — 2010-03-20T11:13:31-04:00 — #3
DELETE FROM C
FROM customers AS C
LEFT JOIN favs AS F ON F.userID = C.id
WHERE F.userID IS NULL
That should remove all the records from the "customers" table that don't have a match in the "favs" table. Change the "DELETE FROM C" line to "SELECT" to see the records.