SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Multiple INNER JOINs

    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:

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mickyginger View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Code:
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •