I have the following tables
//all users deails
smsusers(id,fname , lname ,primary key(id));
//message details of users
//one smsusers can have N messages
user_messages(messageid,message,adddate ,sentby,visibility,
userid,primary key(messageid),foreign key(userid) references smsusers(id),
foreign key(sentby) references smsusers(id));
//One message(user_message) can have N comments
comments(comment_id,comment_on ,commented_by,comment_date,
comment,foreign key(commented_by) references smsusers(id),
primary key(comment_id));
//one message(user_message) can have N post_images
post_images(image_id,small_pic_path,userid,messageid,
foreign key(userid) references smsusers(id),primary key(image_id));
//one message(user_message) can have N likes
likes(element_id,element_type ,liked_by,
foreign key(liked_by) references smsusers(id) ,adddate,
primary key(element_id));
//one smsusers(user) can have 1 profile_pic
profile_pic(pic_id varchar(200),small_pic_path ,userid ,
foreign key(userid) references smsusers(id),primary key(pic_id));
I want to fetch the following details for any messageid and userid of user_messages
1)all details from user_message,
2)last 05 comments related to messageid in ascending order from comments table
(one message can have multiple comments)which includes comment_id ,comment,
comment_date,and details of commented_by(fname,lname,small_pic_path).
3)all small_pic_path from post_images(one message can have multiple images),
4)total likes from like table,
5)all details (smsusers.*,profile_pic.*) of sentby( of table user_messages)
I want to fetch all these details.
Should I use queries or functions to fetch all this information?
Please suggest a query or a function to fetch all the data.
I am using MySQL DB and struts2
You seem to have got the answer. Let me know how was that and if you need anything else.
4) total likes from like table,
5) all details (smsusers.*,profile_pic.*) of sentby
Now join them all in one query
1)all details from user_message
2)last 10 comments related to messageid in ascending order from comments table (one message can have multiple comments)which includes comment_id ,comment, comment_date,and details of commented_by(fname,lname,small_pic_path).
3)all small_pic_path from post_images(one message can have multiple images),
4)total likes from like table,
5)all details (smsusers.*,profile_pic.*) of sentby