Merlin <ngroups@stripped> wrote on 10/04/2005 10:21:00 AM:
> Hi there,
> I just discovered, that I do have some old rows I do not need anymore. A
> result from forgeting to empty the table before starting to go into
> production :-)
> However, I do try to find the rows which are not asociated with another
> table in order to delete them.
> I have 2 tables:
> table1 and table2
> table1 has the key: "ID"
> table2 has the subkey "table1_id"
> Now I would like to delete all rows in table1 which are not listed in
> table2 with an id.
> I tried:
> table1 as t1,
> table2 as t2
> t1.ID != t2.table1_id
> But this returns hundreds of thousends of results.
> I also tryed to group by t1.ID, but it did not help
> Does anybody have a good idea how to get rid of those rows?
> Thanx, Merlin
> PS: Thanx for the answer for the question with full text search! That
> worked excellent!
Use a LEFT JOIN not an INNER JOIN!
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id = t2.table1_id
WHERE t2.table1_id is NULL;
Unimin Corporation - Spruce Pine