Need to find all products without main-category

[passie]

Hi all,

I have a category list with many of them sub categories. My problem is as follows:

I want to have the main-categories (i.e. the categories directly under home only, the one you can see directly without opening any subtree) as a sort of 'summary' of all products that exist in the sub-categories below it. So all products of the sub categories should also be included in their top- parent.

 

Home

- Main1 : prod1, prod2, prod3, prod4

- Sub1 :prod1, prod3, prod4

- Sub2 : prod2

-Main2...

 

 

I implemented this only 'half', i.e. I forgot many times to add it to the main category as well.

Therefore I need to find out easily which products are not in one of the main categories.

 

I had some SQL in mind to do something like this:

 

SELECT * FROM ps_category_product WHERE id_category IN (home, main1,main2) AS a;

 

SELECT * FROM ps_category_product WHERE id_category NOT IN (Home, main1,main2) AS b;

 

SELECT b.* FROM a, b WHERE NOT EXISTS (SELECT b.* from a,b where b.id_product = a.id_product)

 

 

(hope this is correct)

 

 

But how to put this in one single statement, so I can run it in phpMySQL (or the otherwise great RawSQLTool module??)

 

 

any idea??

 

Thanks!

 

Pascal

[PascalVG]

Well, I found a solution. Had some mistakes in first assumptions and mixed up product and category somewhere.

 

This query below should do the trick. Additional 'decoration' with id name and category name in final query result, so here it goes:

 

 

 

create temporary table IF NOT EXISTS mytemptable LIKE ps_category_product;

 

INSERT INTO mytemptable SELECT a.* FROM ps_category_product a WHERE a.id_category NOT IN (<comma separated list of ID's of main categories and Home>);

 

SELECT a.*, b.name, d.name FROM mytemptable a, ps_product_lang b, ps_category_lang d WHERE (NOT EXISTS (SELECT * from ps_category_product c where c.id_product=a.id_product and c.id_category IN (<Same list of main category ID's as above>))) AND (b.id_product = a.id_product) AND (b.id_lang=<id of wanted language>) AND (d.id_category = a.id_category) AND (d.id_lang=<ID of wanted language>) GROUP BY b.id_product ORDER BY b.name

 

As you can see, I added a ; at the end of command 1 (create...) and 2 (Insert...), to run it in one time with command 3

 

This did it for me.

 

Pascal.