Say you have the following simplified tables and data: Category - id - parent_id - name
Product - id - category_id - name - price Category Data 1, NULL, 'GPS' 2, 1, 'Vehicle GPS' 3, 1, 'Handheld GPS' 4, NULL, 'GPS Accessories' Products Data 1, 2, 'Nuvi', 99.99 2, 3, 'eTrex', 99.99 3, 3, 'Montana', 99.99 4, 4, 'Carrying Case', 29.99 Say I want to display only top level categories (parent_id IS NULL), where a particular product matches a certain filter (e.g. price < $100). So because I'm an amateur I first try something like: SELECT categories.name FROM categories WHERE id IN (SELECT category_id FROM products WHERE price < 100.0) AND categories.parent_id IS NULL I quickly realize this will never work because no Products are associated with root category id's. If I know the exact number of levels, I could add another category table. However, I'm wondering if there's a way to do this with recursive CTE and the rcte_plugin that will work no matter how many levels down the products are? I have been reading up on CTE, and while I think I know in general what I need to do, I'm having a hard time understanding CTE's or how to go about this. Any guidance would be greatly appreciated. -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
