Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    16

    Unanswered: joining multiple tables?

    Hello Forum


    I have the following situation:

    IMAGES Table
    PEOPLE Table
    CATEGORIES Table
    CATEGORIES_PEOPLE Table
    PEOPLE_IMAGES Table

    Is there a way to retrieve all people including their images from one category with one mySQL statement?

    I have tried the following but it doesn't work"

    Any insight appreciated.

    Thanks!

    stephank

    -----------------------------------------

    SELECT CATEGORIES_PEOPLE.*,
    CATEGORIES.*,
    PEOPLE.*,
    PEOPLE.PersonName, PEOPLE.Position, PEOPLE.Bio, PEOPLE.Statement,
    IMAGES.Path,IMAGES.ID

    FROM CATEGORIES_PEOPLE, PEOPLE_IMAGES

    INNER JOIN PEOPLE ON CATEGORIES_PEOPLE.PEOPLEID = PEOPLE.ID
    INNER JOIN CATEGORIES ON CATEGORIES_PEOPLE.CategoryID = CATEGORIES.ID
    INNER JOIN IMAGES ON PEOPLE_IMAGES.IMAGESID = IMAGES.ID

    WHERE CATEGORIES_PEOPLE.CategoryID = '1'

    ORDER BY CATEGORIES_PEOPLE.OrderNum

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stephank
    FROM CATEGORIES_PEOPLE, PEOPLE_IMAGES

    INNER JOIN PEOPLE ON CATEGORIES_PEOPLE.PEOPLEID = PEOPLE.ID
    INNER JOIN CATEGORIES ON CATEGORIES_PEOPLE.CategoryID = CATEGORIES.ID
    INNER JOIN IMAGES ON PEOPLE_IMAGES.IMAGESID = IMAGES.ID
    please, don't do that

    you are mixing the old style comma joins with the new style JOIN syntax

    first of all, if you already know which category (based on your WHERE clause), then you do not need the CATEGORIES table at all

    this also means you should start your joins at the CATEGORIES_PEOPLE table

    Code:
      FROM categories_people
    INNER 
      JOIN people 
        ON people.id = categories_people.peopleid 
    INNER 
      JOIN people_images 
        ON people_images.peopleid = people.id
    INNER 
      JOIN images 
        ON images.id = people_images.imagesid
     WHERE categories_people.categoryid = 1
    please note carefully the sequence of tables in the joins

    also, note that if categoryid is numeric, you want to compare it to the number 1, not the string '1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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