从表中获取数据所需的单个查询 [英] Required single query to fetch data from 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));

我想获取user_messages的任何messageid和userid的以下详细信息

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.

我正在使用MySQL DB和struts2

I am using MySQL DB and struts2

推荐答案

4)来自喜欢表的喜欢总数，

4) total likes from like table,

SELECT count(*) AS total_likes FROM likes WHERE element_id = <messageID>;

5)发件人的所有详细信息(smsusers.*，profile_pic.*)

5) all details (smsusers.*,profile_pic.*) of sentby

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>)

这篇关于从表中获取数据所需的单个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！