[Resolved] comparing database entries efficiently?

[MattG]

Hi,

I have two table in a database, one with products one with reviews. The reviews are linked to the product via a unique id but I'm looking to write a script which can see if a product in the database has been reviewed or not but I hit a "coders block"

I was going to use a loop to see if the id in the review table matched any in the products table but I would prefer the script to only show the ones which haven't been reviewed and so that would not be the best way since it would chuck out a 'False' every time it didn't match up.

Can anyone suggest a way of doing it?

[wilku]

Would such query work?

SELECT products.id
FROM products
LEFT JOIN reviews ON products.id = reviews.product_id
WHERE reviews.product_id IS NULL

I tried to do it with subquery, but it didnt work (because I've got mysql < 4.1):

SELECT products.id
FROM products
WHERE products.id NOT IN (
SELECT reviews.product_id
FROM reviews);

The idea for both queries is taken from mysql manual

[MattG]

top one worked perfectly - thanks very much for your help.