please help with adding AND statement to my query

[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

[Jack_mcs]

Change

 and p.products_status = '1' and p2c.categories_id = '" . (int)$category_id . "'");

to

 and p.products_status = '1' and p.products_id= atr.products_id AND atr.options_values_id='772' and p2c.categories_id = '" . (int)$category_id . "'");

[marcus76]

thanks for the suggestion Jack, unfortunately it didn't return anything:

 

$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 p.products_id= atr.products_id AND atr.options_values_id='772' and p2c.categories_id = '" . (int)$category_id . "'");

 

without the : and p.products_id= atr.products_id AND atr.options_values_id='772'

 

my category product count displays (16) with the addition of the above, i'd expect it to drop to (6) but it displays nothing.

 

any ideas?

[marcus76]

...just to add to this. i don't know SQL so pls correct my misunderstanding...could it be that.

 

it finds the product_id's for the atr.options_values_id='765'

 

but then it returns a different product_id for atr.options_values_id='772' as it finds the 1st record, where there's actually several and eventually in the table there will be one that exists that matches '772'..

 

and therefore it's not a matching product_id

 

so = no results

 

;-(

[marcus76]

ok, driving me crazy...real basics now..i'm testing the query in phpmyadmin.

 

my table looks like this:

 

products_id | options_values_id

 

613 | 600

719 | 600

719 | 601

719 | 602

 

SELECT products_id FROM products_attributes WHERE (options_values_id ='600')

 

returns: (2 product ID's) i.e:

613

719

 

SELECT products_id FROM products_attributes WHERE (options_values_id = '600' AND options_values_id ='601')

 

returns (empty), i'd expect

 

719

 

???? what gives

[♥geoffreywalton]

options_values_id on one record can not equal 2 different values at the same time

 

You mean not equal

 

SELECT products_id FROM products_attributes WHERE (options_values_id != '600' AND options_values_id !='601')

 

try the sql section of http://www.w3schools.com/

[marcus76]

TOP MAN!

 

many thanks Geoffrey

 

works a treat.

[marcus76]

sorry, i replied too quick:

 

i do actually mean to have this - options_values_id on one record can not equal 2 different values at the same time

 

how best could i achieve it?

[marcus76]

for those interested, this works:

 

SELECT products_id FROM products_attributes WHERE options_values_id in ('600','601') group by products_id HAVING count(*) = 2

 

;-)