How to Join three tables?

kamy99

Hi all:

I always get confused at joins, so please bear with me.

In my DB, I have these tables:

Pictures (ID, Path),
Category (ID, Name)
Organized (ID, CatID, PicID)

I had to do it this way, because it's a many-to-many situation.
(A pic can go into many cats, a cat may contain many pics)

So now I need to retrieve from the Organized table.

My initial page, retrieves all the cats (no problem there), so after selecting a category, I wanna display all the pictures from that category from the Organized table.

All help is wlcome!

Happy Holidays!

arelgee

I'm new to PHP but have been working with DB's for a while. I use a create view instead of a join whenever possible. Depending on what you are doing, you can query the view, update delete, etc, and the changes will be made to the underlying tables.
Hope this helps;
arelgee

Cong

Depends on what you want to do, here's a straight joins.

Pictures (ID, Path),
Category (ID, Name)
Organized (ID, CatID, PicID)

SELECT P.ID, P.Path FROM
Pictures P, Category C, Organized O
WHERE
C.ID=O.CatID AND O.PicID=P.ID AND C.Name="$something"

As you can see you no longer need the ID in organized just make CatID and PicID a set of multi-unique keys.