[RESOLVED] LEFT JOIN ISSUE

[jamesmu]

I previously had the following sql statement

SELECT * FROM tbl_folder WHERE folder_fix='1' ORDER BY folder_title

but decided to change it to the following so as to pull information from a related table

SELECT * FROM tbl_folder LEFT JOIN tbl_folderimage ON tbl_folder.folder_id = tbl_folderimage.folder_id LEFT JOIN tbl_image ON tbl_folderimage.image_id = tbl_image.image_id WHERE tbl_folder.folder_fix='0' GROUP BY tbl_folder.folder_id ORDER BY folder_title

it works great pulling the correct information from the tables but for some reason its not outputing the folder_id in my results where an entry in the joined tbl_folderimage is not present, it does output other fields from this table. is this because im using the folder_id to make the join?

what am i doing wrong

thanks

[djjjozsi]

image_folderimage is some kind of a connector table?

The tbl_folder isn't in direct connection with tbl_image.

I added a field , that should be the image name: tbl_image.image_NAME

SELECT tbl_folder.folder_id, tbl_folder.folder_title, tbl_image.image_NAME
FROM tbl_image RIGHT JOIN (tbl_folderimage RIGHT JOIN tbl_folder ON tbl_folderimage.folder_id = tbl_folder.folder_id) ON tbl_image.image_id = tbl_folderimage.image_id
WHERE (((tbl_folder.folder_fix)='0'))
GROUP BY tbl_folder.folder_id, tbl_folder.folder_title, tbl_image.image_NAME;

[jamesmu]

thanks djjjozsi

that did the trick, i didnt know about right join ill have to read up on it

tbl_folderimage is indeed a connector table

thanks a lot

[djjjozsi]

Your welcome.

If you start writing the query from the tbl_folder LEFT JOIN should used between the tables: folder and (Images).
that is another viewpoing, but the technique is the same.
I always start the query from the farest tables

Don't forget to set this thread as resolved. 🙂

Hello,
jjozsi.