Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Double inner join

    Ok.. heres the deal.. im making one of those fancy navigation bars where
    you can see how deep and where you are in the navigation structure like this:
    each of them is a link.. all i need in each of them is the id that belongs to the name.

    tutorials -> scripting -> php

    I have 3 tables:

    section:
    sectionID
    sectionName

    category:
    catID
    sectionID
    catName

    subCat:
    subCatID
    catID
    sectionID
    subCatName


    lets say i browse the subCategories..
    I want to use a query that lets me:
    [retrieve sectionName by joining subCat.sectionID on section.sectionID]
    and
    [retrieve catName by joining subCat.catID on category.catID]

    Thus i shall end up with 6 values: subCatID and name, catID and name, sectionID and name.

    so far ive built this query:
    Code:
    SELECT section.sectionName, subCat.sectionID, category.catName, subCat.catID, subCat.subCatID, subCat.subCatName
    FROM section, category, subCat
    INNER JOIN category ON category.catID = subCat.catID 
    INNER JOIN section ON section.sectionID = subCat.sectionID
    WHERE subCat.subCatID = '$subCatID'";
    But it doesnt work.
    do anybody know have a clue about how my query needs to look like?
    Last edited by Icecube; 02-20-03 at 17:44.

  2. #2
    Join Date
    Feb 2003
    Posts
    5
    I believe that the inner join syntax is should be something like this:


    SELECT section.sectionName, subCat.sectionID, category.catName, subCat.catID, subCat.subCatID, subCat.subCatName
    FROM (subCat INNER JOIN category ON category.catID = subCat.catID)
    INNER JOIN section ON section.sectionID = subCat.sectionID
    WHERE subCat.subCatID = '$subCatID'";


    rather than using all three tables followed by the inner join statements in the From clause...

    If you check the MySql manual it has a pretty good example of chaining Inner Joins...

  3. #3
    Join Date
    Feb 2003
    Posts
    5
    ok thanx
    Last edited by Icecube; 02-21-03 at 11:46.

  4. #4
    Join Date
    Feb 2003
    Posts
    5
    couldnt find the article... got an url?

  5. #5
    Join Date
    Feb 2003
    Posts
    5

    Red face manual

    The mySQL manual itself is located at http://www.mysql.com/doc/search.php
    but the article on chaining inner joins is not there. I must have gotten confused with a SQL tutorial that I read in a book. Sorry. Basically the parentheses act in a similar manner to parentheses in mathematical statements, forcing the computer to perform those operations first. In this case, it allows you to join 2 tables first and then join the 3rd to it. If they are unnecessary, MySQL ignores them anyways.

Posting Permissions

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