Really not sure how to do this.. but I suspect a sub select is needed

I have 2 tables, both have a column labeled ID , I need to delete the
row in tableONE whenever a matching ID was NOT found in tableTWO ..
Sounds simple.. and probably is, I'm just not finding the right
incantation.

On 23 Sep, 16:47, dewed <duane.lor... (AT) gmail (DOT) com> wrote:
You want a LEFT JOIN on the ID with the WHERE clause specifying
WHERE tableTWO.ID IS NULL

Thanks for the cluestick ..

Here is what I came up with . . . testing as a select first, just to
be safe

select tableONE.ID , tableTWO.ID
FROM tableONE
LEFT JOIN tableTWO ON tableONE.ID = tableTWO.ID
WHERE tableTWO.ID IS NULL


The query appears to work as intended, but currently finds no matches,
which is an unexpected result. I would of thought there would be at
least a few matches, so I'd apreciate if you could let me know if the
query above looks correct to you.. or do you need a bigger cluestick :P

dewed wrote: [...]


Looks fine to me.

Here I have a similar scenario, with the tables "orders" and orderRows",
both with column orderID: your sintax does find the orders which are not
referenced by any orderRow. In my case, orderID is an external key of
table orderRows, but I do not think that it affects the result.
I have then used the following sintax to delete the orders:

DELETE tableONE
FROM tableONE
LEFT JOIN tableTWO ON tableONE.ID = tableTWO.ID
WHERE tableTWO.ID IS NULL;


Regards
--
Enos

On 23 Sep, 17:46, dewed <duane.lor... (AT) gmail (DOT) com> wrote:
That looks perfectly correct. Why not add a dummy record to tableONE
only and run the query again to check that that record is indeed
returned?