[SOLVED] SQL Statement for selecting products

[brownd92]

Hi there,

I need help with a SQL statement for a multistore, something like:

 

Select all products from SHOPID where MANUFACTURER = 'ManufacturerID'

 

 

Thanks

[vekia]

Hi there,

I need help with a SQL statement for a multistore, something like:

 

Select all products from SHOPID where MANUFACTURER = 'ManufacturerID'

 

 

Thanks

select * from ps_product AS p
INNER JOIN ps_product_shop AS ps on ps.id_product=p.id_product
WHERE p.id_manufacturer='1' AND ps.id_shop='1' 

you need to define onw id_manufacturer and own id_shop values

[brownd92]

Excellent! Thank you for your help

One more question if you do not mind.

How would I make an update statement to say set all products active = 0 where manufacturer id = 1 and shop id = 1

 

 

Thanks again

[brownd92]

Essentially I need to set products from different manufacturers either active or not in different shops :-)

[vekia]

use this:

update ps_product_shop AS ps
INNER JOIN ps_product AS p on ps.id_product=p.id_product
SET ps.active=0
WHERE p.id_manufacturer='1' AND ps.id_shop='1' 

[brownd92]

Excellent! Thank you very much!

[vekia]

you're welcome

i marked this topic as [solved]

if you need any other queries related to this case - just continue discussion below

 

with regards,

Milos