I'm putting together a product filter system for an online shop where users can filter by brand and material, amongst other things. The options are in <select> dropdowns, and they currently reference separate tables in the database and these (if selected) and add to the query limiting which records are returned. What I was hoping to do is only display brands/materials which will return any results. I've played with an inner join but this returns the same brand/material as many times as it matches with products, which is not much use.
To give an idea of the schema, for this part of the filter, I'll be using the following tables:
The associations table contains the primary keys for each record so product->brand->material->.
What I want to avoid is, for example the material glass appearing for sofas in the dropdown, so I could just limit it to leather, fabric etc.
I hope this makes sense, any help will be greatly appreciated.