将两个查询合并为一个
我正在从多个表中提取数据。 并试图solve this将两个查询合并为一个
最后我得到两个查询
1),其获取用户信息的培训相关的数据。
2)获取所有评论及其细节。
我想在一个查询中组合这两个查询。
查询1)
SELECT user_messages.messageid,user_messages.message,
user_messages.sentby,user_messages.visibility,
GROUP_CONCAT(post_images.image_id SEPARATOR ';')
AS `POST_IMG_ID`,
GROUP_CONCAT(post_images.small_pic_path SEPARATOR ';')
AS 'POST_IMG_PATH',
(
SELECT count(*) FROM likes
WHERE element_id=user_messages.messageid
)AS 'TOTAL_LIKES',
smsusers.fname as 'SENTBY_FNAME',
smsusers.lname as 'SENTBY_LNAME',
profile_pic.small_pic_path as 'SENTBY_SMALL_PIC_PATH'
FROM user_messages
INNER JOIN smsusers ON
user_messages.SENTBY = smsusers.id
INNER JOIN profile_pic ON
user_messages.SENTBY = profile_pic.userid
left outer JOIN post_images ON
user_messages.messageid=post_images.messageid
WHERE user_messages.userid='1'
GROUP BY user_messages.messageid
order by user_messages.adddate
其工作环节Sql Fiddle
查询2)
SELECT comments.comment_id, comments.comment, comments.comment_date,
smsusers.fname ,smsusers.lname,profile_pic.small_pic_path
FROM comments, smsusers , profile_pic , user_messages
WHERE user_messages.messageid = 'm1'
AND user_messages.userid = smsusers.id
AND smsusers.id = profile_pic.userid
ORDER BY comment_date
LIMIT 0, 10;
工作环节Sql Fiddle
使用的部份查询和删除相关子查询
SELECT user_messages.messageid,
user_messages.message,
user_messages.sentby,
user_messages.visibility,
GROUP_CONCAT(post_images.image_id SEPARATOR ';') AS `POST_IMG_ID`,
GROUP_CONCAT(post_images.small_pic_path SEPARATOR ';') AS 'POST_IMG_PATH',
likes.TCount AS 'TOTAL_LIKES',
smsusers.fname AS 'SENTBY_FNAME',
smsusers.lname AS 'SENTBY_LNAME',
profile_pic.small_pic_path AS 'SENTBY_SMALL_PIC_PATH',
GROUP_CONCAT(comments.comment) AS `Comments`
FROM user_messages
INNER JOIN smsusers ON user_messages.SENTBY = smsusers.id
INNER JOIN profile_pic ON user_messages.SENTBY = profile_pic.userid
LEFT OUTER JOIN post_images ON user_messages.messageid = post_images.messageid
LEFT JOIN
(SELECT element_id,
COUNT(*) AS `TCount`
FROM likes
GROUP BY element_id) AS likes ON likes.element_id = user_messages.messageid
LEFT JOIN comments ON comments.comment_on = user_messages.messageid
WHERE user_messages.userid = '1'
GROUP BY user_messages.messageid
ORDER BY user_messages.adddate;
感谢您的回复。这里'POST_IMG_ID'和'POST_IMG_PATH'重复多次 – 2013-03-05 13:17:34
我有一个更多的请求,我们可以根据'comments.comment_date' – 2013-03-05 13:19:58
排序'COMMENTS'我想要获取'smsusers.fname'和'smsusers.lname '的评论者和'profile_pic.small_pic_path'的评论者 – 2013-03-05 13:35:30
问题是......? – Matten 2013-03-05 11:23:22
你有没有尝试过自己,或者你只是想让别人为你做你的工作? – fancyPants 2013-03-05 11:26:35
你的需求量是多少? – 2013-03-05 11:37:01