Multiple INNER JOINs

[mickyginger]

Hi there,

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 `cataloguesections` and `cataloguecategories`. If I just want to search for sections for example I can run this query:

SELECT `catalogue`.`id`, `title`
FROM `catalogue`
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?

Many thanks,
Mike

[r937]

Do I need to create a UNION of 2 queries?

depends if you want to OR or AND the results of the separate searches

your example shows catalogs in section 1

what if you want also to search for catalogs in category 3?

do you want catalogs that belong to section 1 OR category 3? or should they be in both?

use a UNION if it's OR

use a 5-table join if it's AND

[mickyginger]

use a UNION if it's OR

use a 5-table join if it's AND

Thanks, very straightforward in the end. Here's my final query:

SELECT `catalogue`.`id`, `title`
FROM `catalogue`
INNER JOIN `cataloguesections` ON `cataloguesections`.`catalogueid` = `catalogue`.`id`
INNER JOIN `sections` ON `sectionid` = `sections`.`id`
INNER JOIN `cataloguecategories` ON `cataloguecategories`.`catalogueid` = `catalogue`.`id`
INNER JOIN `categories` ON `categoryid` = `categories`.`id`
WHERE `categories`.`id` = x
AND `sections`.`id` = y

M