Not EXISTS

harman

Hi guys, can any one tell me whats wrong with this statement

SELECT product_id
FROM special_pricing s
WHERE NOT
EXISTS (
SELECT *
FROM products p
WHERE p.prod_id = s.product_id)

What i am trying to do here is i have 2 tables, products and special_pricing with product_id=prod_id...i have deleted some entries from the product table but entries with the same prod_id are still in the special_pricing table, i am trying to list those entries.

AstroTeg

Which database engine are you using?

harman

mySQL

Sxooter

Either switch databases, or spend some time reading up in the MySQL manual how to make these kinds of things work with joins.

I'd recommend postgresql or firebirdsql, both full featured, reliable, and fast.

AstroTeg

You may want to look here:

http://www.mysql.com/doc/en/Subqueries.html

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and and other methods. See section 13.1.8.11 Rewriting Subqueries for Earlier MySQL Versions.

Basically, if you're using MySQL v4.0 or less, subselects are not available and EXIST will not work (just tried it on MySQL v4.0.18).

Sxooter

AstroTeg, do you know if 4.1 supports exists yet? Just wondering.

harman

thanks astro.

AstroTeg

I don't have any boxes setup with v4.1 so I don't know. From reading the docs, it looks like v4.1 supports it.