Required single query to retrieve data from tables
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 get the following data for any messageid and userid 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 get all these details.
Should I use queries or functions to retrieve all of this information?
Please suggest a query or function to retrieve all data.
I am using MySQL DB and struts2
source to share
1)all details from user_message
SELECT * FROM user_messages WHERE userid = <userID> AND messageid = <messageID>;
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).
SELECT a.comment_id, a.comment, a.comment_date, b.fname || b.lname || c.small_pic_path "Commented by"
FROM comments a, smusers b, profile_pic c, user_messages d
WHERE d.messageid = <messageID>
AND d.userid = b.id
AND b.id = c.userid
ORDER BY comment_date
LIMIT 0, 10;
3)all small_pic_path from post_images(one message can have multiple images),
SELECT small_pic_path
FROM post_images;
4)total likes from like table,
SELECT * FROM likes;
5)all details (smsusers.*,profile_pic.*) of sentby
You have not posted the structure of sentby
source to share
4) the total number of liked similar tables,
SELECT count(*) AS total_likes FROM likes WHERE element_id = <messageID>;
5) all details (smsusers. *, Profile_pic. *) Sent
SELECT smsusers.*,profile_pic.* FROM user_messages
JOIN smsusers
ON user_messages.sentby = smsusers.id
JOIN profile_pic.userid = smsusers.id
WHERE user_messages = <messageID>
Now join them all in one query
SELECT (<Query_1>),(<Query_2>),[...],(<Query_N>)
source to share