Creating A Sql Query Retrieving Products Without A Specific Product Feature Specified

[Diffusio]

We are a web SEO company in business since fall 2011. We are located in Canada. Our staff is composed of a web strategist and two SEO specialists, one in French and the other in English. Our team is able to work with html, CSS, php, Java Script, etc. But we are not programmers! In the short term, we have some things to be done by a programmer for a Prestashop e-commerce website.

 

Creating a SQL query retrieving products without a specific product feature specified in Prestashop

 

Our first project consist of creating a SQL query retrieving all products which do not have a specific product feature specified (for example, list all products for which the "microwave safe" feature is not specified)). We already have built a query named "List products without manufacturers" in the SQL Manager of Prestashop. We would like to have similar queries for each feature. As you should know, "features" are not stored in the Prestashop database in the same table as the "features". That is why we need your help.

 

We say this is the first project because we are looking for someone to do programming jobs on a regular basis for our website

[Daresh]

This query will select all product IDs that don't have a specific feature set:

SELECT `p`.`id_product` FROM `ps_product` `p` WHERE `p`.`id_product` NOT IN (SELECT `id_product` FROM `ps_feature_product` WHERE `id_feature` = 5)

Where you need to change "5" to ID of the feature that you are interested in.

 

This query will select all product IDs that don't have a specific attribute set:

SELECT `id_product` FROM `ps_product` WHERE `id_product` NOT IN (SELECT DISTINCT `ppa`.`id_product` FROM `ps_product_attribute` `ppa` WHERE `ppa`.`id_product_attribute` IN (SELECT `ppac`.`id_product_attribute` FROM `ps_product_attribute_combination` `ppac` WHERE `ppac`.`id_attribute` IN (SELECT `id_attribute` FROM `ps_attribute` WHERE `id_attribute_group` = 1)))

The "1" at the end is attribute group ID and needs to be changed accordingly.

 

Queries assume that your database prefix is 'ps_' and I will be happy to cooperate with you

Edited March 19, 2016 by Daresh (see edit history)