I'm creating a sort of forum/wall where users can post examples of their work in either photo or video format but I want a query which will get all of these posts and their attachments however if a video is attached it will only get all of the video details but if a photo is attached it will only get the photo details.
Is there any way to do this in the following query or will I simply need to join onto both tables the way I have currently?
Is this the only feasible way of doing this?
SELECT posts.id, posts.text, posts.attachment_id, posts.attachment_type, videos.id, videos.name, videos.source, photos.id, photos.name, photos.source
LEFT JOIN videos
ON (posts.attachment_id = videos.id)
LEFT JOIN photos
ON (posts.attachment_id = photos.id)
I also have a secondary problem. Users are able to comment on these posts but when the wall is loaded I only want the first 3 comments for each post to be displayed. Is this possible inside the above single query?
At the moment my comments table includes only id and comment_text and my join table between comments and posts contains id1 and id2 with id1 being the id of the post the comment is on and id2 being the id of the comment.
Any help would be greatly appreciated.