[stephank]

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

[healdem]

as ever: the manuel is your amigo.

http://dev.mysql.com/doc/refman/5.0/en/join.html

[r937]

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

  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'