Problems with MariaDB output

Question

I'm trying to create a menu based in MariaDB tables. We have a associative table, which connects with other tables

#ass_products_categories
product_id | category_id
-------------------------
1          | 1    <- GENDER
1          | 3    <- AGE
1          | 16   <- TYPE
2          | 2
2          | 3
2          | 16
3          | 2
3          | 4
3          | 17
4          | 1
4          | 2
4          | 3
4          | 16

The columns references to products table and categories table, obviously

#products
id | name          | qty | is_published
---------------------------------------
1  | T-Shirt Black | 10  | 1
2  | Long Dress    | 0   | 1
3  | T-Shirt Blue  | 10  | 0
4  | T-Shirt Red   | 10  | 1

#categories
id | name        | tipology_id
------------------------------
1  | Man         | 1 <- GENDER
2  | Woman       | 1 
3  | 3 a 5 years | 2 <- AGE
4  | 6 a 7 years | 2
16 | T-Shirt     | 4 <- TYPE
17 | Dress       | 4

This is the output I expect.

[Man]
---- [3 A 5 years]
--------- T-Shirt

[Woman]
---- [6 a 7 years]
--------- T-Shirt
--------- Dress

I can do it with PHP and a lot of loops, but I know is possible to make this with only a good query. But I'm really stuck in a lot of left joins and inner joins.

The point is that: if there's nor a single product published in a category or all products in that category are qty = 0, the category should not appear in menu. The nearest approach I've found was WITH, but it's not possible/available in MariaDB (at least not in the host MariaDB server)

Sorry my bad english, please. Thanks for helping.

Answer 1

This is a very complex operation but just for join query idea. Check here

SELECT DISTINCT gender.name, 
                age.name, 
                type.name 
FROM   ass_products_categories a 
       JOIN products p
         ON p.id = a.product_id and is_published = 1
       JOIN (SELECT c.name, 
                    product_id 
             FROM   ass_products_categories pc 
                    JOIN categories c 
                      ON c.id = pc.category_id 
                         AND c.tipology_id = 1) gender 
         ON gender. product_id = a.product_id 
       JOIN (SELECT c.name, 
                    product_id 
             FROM   ass_products_categories pc 
                    JOIN categories c 
                      ON c.id = pc.category_id 
                         AND c.tipology_id = 2) age 
         ON age.product_id = a.product_id 
       JOIN (SELECT c.name, 
                    product_id 
             FROM   ass_products_categories pc 
                    JOIN categories c 
                      ON c.id = pc.category_id 
                         AND c.tipology_id = 4) type 
         ON type.product_id = a.product_id