CODEWITHSUNDEEP
mysqlexists
nakins
nakins

Reputation: 13

MySQL how to select on multiple tables using Not Exist

I have three tables. One is a table of deletion candidates. This table was created with certain criteria, but did not include a couple of factors for consideration (limitations of the system). The other two tables were created considering those "left out" factors. So, I need to run a SELECT query on these three tables to come up with a deletion list.

What I started with is:

SELECT inactive.id
FROM inactive, renamed, returned
WHERE NOT EXISTS (inactive.id = remamed.id and inactive.id = returned.id)

But this is giving me an error. Can someone point out my error here?

Thank you

Upvotes: 1

Views: 5310

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

It's not entirely clear what you are trying to do here.

I assume you want a list of all rows from the inactive table that do not exist in either the renamed table or the inactive table. Is that right?

If so you can use a query like this:

SELECT inactive.id
FROM inactive
WHERE NOT EXISTS (select null from renamed where renamed.id = inactive.id)
AND NOT EXISTS (select null from returned where returned.id = inactive.id)

Upvotes: 10

Related Questions