[Resolved] sql help

[TravisB]

I have a query where I am selecting from two tables. I want to select rows where a product id exists in one table but not the other.

This is what I have come up with, but it does not work. There are 341 products, and 2 of them are also in the specials table. So it should tell me that there are 339 products that are in the products table and not in the specials table but I get 680.

select products.products_id, specials.products_id from products, specials where products.products_status = '1' and products.products_id <> specials.products_id

[tuf-web_co_uk]

products.products_status = '1'

where have you selected products.products_status from the table using sql?

[paulnaj]

Here's why you're getting 680 ...

The select without the where/and condition ...

select products.products_id, specials.products_id
from products, specials

... will produce a result of the form ...

products.products_id specials.products_id
1 1
1 2
2 1
2 2
3 1
3 2
...
341 1
341 2

There will be 2*341=682 results

If you put the restriction 'products.products_id <> specials.products_id' on this table, only two items will fail, (1st and 4th item), thus leaving 680 items in the result.

I'll have to have a think about the best way to get the result you want ... I'm sure there's a 'in_array' style mysql function that'll help but two calls to the database might be necessary.

[paulnaj]

This seems a reasonable way to do it.

$res = mysql_query('SELECT products_id FROM specials');
while($row = mysql_fetch_row($res)){
$special_ids[] = $row[0];
}

$res = mysql_query('SELECT products_id FROM products WHERE products_id NOT IN ('.join(',', $special_ids).')');

But, this will get a bit unwieldy if there are going to be lots of 'specials'.

You could do something like ..

$res = mysql_query('SELECT products_id FROM products');
while($row = mysql_fetch_row($res)){
$product_ids[] = $row[0];
}

$res = mysql_query('SELECT products_id FROM specials');
while($row = mysql_fetch_row($res)){
$special_ids[] = $row[0];
}

$not_specials = array_diff($product_ids, $special_ids);

(All products that are not specials)

Someone else might have a quicker way, but I don't see how you'd be able to do it in one call to mysql (have to wait for version 5).

[TravisB]

Thanks paulnaj. That looks like it might work. I will try that when I get back to work on Monday.

If anyone else knows of another solution, I would like to see that as well.

[TravisB]

Thanks, that works just like I wanted it to.

[paulnaj]

You're welcome.

P.