i am having a problem with trying to join 4 tables
i am trying to show a list of images for product type
i have category table that is shorted to "<b>cat</b>" that contains categories like "rings", "ear rings", "bracelets", "winter collection", "summer collection"
then i have a table called "<b>links</b>" so i can link the categories to items that are in the "<b>items</b>" table, the resion i have the links table is because an item can be in 2 or more categories an individual ear ring will be in the "ear ring" category but also "winter collection" or "summer collection".
then there is a media table witch contains the names of the images linked to an item, some will have one image some will have many so once i have my list of items i need be able to pick just one of the images by only selecting an image with "ord" (order) that is 0, the id in the items table links to link_id in the media table
so basically i want to select a all the items from one of the categories then get a list of images that relate to them. at the moment my SQL looks like this.
SELECT * FROM cat
JOIN links ON cat.id = links.cat_id
JOIN items ON items.id = links.item_id
JOIN media ON media.link_id = links.id
WHERE cat.id = 6 AND media.ord = 0
ORDER BY links.ord ASC
at the moment this works but it dose not let me get at values in the other tables link media.file_name or items.name_html
i have only just started to JOIN staments and getting really confused with the syntax and i still have no idea what a LEFT or RIGHT join is meant for!
this is my data base design in case what i have written is a little confusing, you can see what i am trying to join in the purple hope this helps to make more sense: