Rewriting Not Exists as a Left Join

[FalseDawn]

Been a bit stumped by this for a while.
I am trying to rewrite a NOT EXISTS query as a Left Join for MySQL versions prior to 4.1.

I know the general idea, but in my case, I need (in effect) a subset of the table on the right of the join, and I just can't get it to work.

My Not Exists Query is:

SELECT MH.product_code FROM merchant_holding MH WHERE NOT EXISTS (SELECT * FROM product P WHERE P.product_code=MH.product_code AND P.merchant_id=9999) AND MH.flag=1

So you can see that I am interested in only the subset of the product table where the merchant is 9999.
This works fine, and gives me product codes that do not exist in the product table (for merchant 9999)

I have tried:
SELECT merchant_holding.product_code FROM merchant_holding LEFT JOIN product ON merchant_holding.product_code=product.product_code WHERE (product.product_code IS NULL and product.merchant_id=9999)

Which doesn't work, since I imagine that the merchant_id is NULL in the LEFT JOIN, and the "WHERE product.merchant_id=9999" condition excludes this row.

I tried fudging it using extra conditions:
SELECT merchant_holding.product_code FROM merchant_holding LEFT JOIN product ON merchant_holding.product_code=product.product_code WHERE (product.product_code IS NULL and product.merchant_id=9999) OR (product.product_code IS NULL AND product.merchant_id IS NULL)

This just hangs the mysql engine (the product and merchant tables are 40K and 2K rows respectively, so I imagine a huge temp dataset is being created somehow).

Any ideas anyone?

[Demaestro]

You have:

SELECT merchant_holding.product_code FROM merchant_holding LEFT JOIN product ON merchant_holding.product_code=product.product_code WHERE (product.product_code IS NULL and product.merchant_id=9999)

Try: I switched the 'and' condition to an 'or' condition

SELECT merchant_holding.product_code FROM merchant_holding LEFT JOIN product ON merchant_holding.product_code=product.product_code WHERE (product.product_code IS NULL OR product.merchant_id=9999)

But I am not sure if this is what you want. Your post is a little confusing becuase at the top you say your first query is working. So why are you changing it? Not trying to be difficult just want to help and not sure what was wrong with the first query.

[coopster]

for MySQL versions prior to 4.1

It's a workaround for prior versions.

[FalseDawn]

Thanks for the suggestion, but using OR didn't work - it just caused the engine to hang again.
Looking at the EXPLAIN output, the query cannot use the indexes for the join when certain conditions are in the WHERE.

Anyway, the solution is no longer needed as I have upgraded the mysql version (should have done so a long time ago!)