please help with adding AND statement to my query

i can add 1 AND but not a 2nd one?

[marcus76]

Hi All,

Can someone pls give me some pointers to how i go about adding an additional AND statement to my query below. I have the following that works fine:

$products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, products_attributes AS atr where p.products_id= atr.products_id AND atr.options_values_id='765' and p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '" . (int)$category_id . "'");

I need to return the results with the additional condition added:

where p.products_id= atr.products_id AND atr.options_values_id='772'

I think i may need to query the results of the 1st query to achieve this? if so how?

Thanks for any help.

Kind Regards

Marc

[phranque]

i think this is what you want:
$products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, products_attributes AS atr where p.products_id= atr.products_id AND atr.options_values_id IN (765, 772) and p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '" . (int)$category_id . "'");

[marcus76]

thanks, that helped...i've done the following, which returns 9 results (correct) however the 'total' count value = 2

any ideas how i can get this to be 9 ?

i have the following:

select count(*) as total from products p, products_to_categories p2c, products_attributes AS atr where p.products_id = atr.products_id AND atr.options_values_id in ('765','772') and p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '43' GROUP by p.products_id HAVING count(*)=2