I have a catalogue database. The db contains products which are organised into sections, and categories. One product may exists in many sections and many categories.
I am creating a search engine for the administrator to search for products. I am a little confused about creating multiple INNER JOINs on one table.
I have 2 lookup tables
cataloguecategories. If I just want to search for sections for example I can run this query:
SELECT `catalogue`.`id`, `title`
INNER JOIN `cataloguesections` ON `catalogueid` = `catalogue`.`id`
INNER JOIN `sections` ON `sectionid` = `sections`.`id`
WHERE `sections`.`id` = '1';
But I'm not sure how to search for both sections and categories. Do I need to create a UNION of 2 queries?