I'll try not to confuse you.
Table products -> product_id -> name -> image Table products_to_category -> product_id -> category_id Table category -> category_id -> parent_id Table category_description -> category_id -> name
As you may already know, I can have multiple parents/categories. Is the following example:
Category Parent Computer Category Parent Hardware -> Category Motherboard -> Category Parent CPU -> Category INTEL -> Category AMD Category Parent Software -> Category Microsoft -> Category Apple Category Parent Accessories -> Category Headsets -> Category Mouses
...And so on.
What I want is by giving the FIRST Category Parent ID (in this case, "Computer" OR "Accessories"), for example '600', I would like to retrieve only ONE image from ANY category of that Parent Category.
This is what I've tried so far:
SELECT prod.image FROM product AS prod JOIN product_to_category AS prod_to_category ON prod_to_category.product_id = prod.product_id JOIN category AS cat ON cat.category_id = prod_to_category.category_id WHERE cat.parent_id = 600 AND prod.image NOT NULL ORDER BY RANDOM() LIMIT 1
If I change the "cat_parent_id = 600" to "cat_parent_id = 720" (which is the Category Parent "Hardware"), it works fine.
With the current code, I get no output.
How can I achieve what I want?