用户GROUP BY ERROR之间的SQL查询privatemsgs

I've build the following query:

我构建了以下查询:

(SELECT 
    privatemsgs.id,
    privatemsgs.useradn,
    privatemsgs.useraid,
    privatemsgs.title,
    privatemsgs.created,
    privatemsgs.timee,
    privatemsgs.isread,
    u.photo AS creatorphoto,
    privatemsgs.relatedto
FROM privatemsgs 
LEFT JOIN
    users AS u ON(privatemsgs.useraid = u.id)
WHERE userbid='5'
    AND relatedto=0 and bdel=1)
UNION ALL
(SELECT
    privatemsgs.id,
    privatemsgs.useradn,
    privatemsgs.useraid,
    privatemsgs.title,
    privatemsgs.created,
    privatemsgs.timee,
    privatemsgs.isread,
    u.photo AS creatorphoto,
    rel.relatedto
FROM privatemsgs AS rel
    JOIN privatemsgs ON(rel.relatedto = privatemsgs.id)
    LEFT JOIN
    users AS u ON(rel.useraid = u.id)
WHERE rel.userbid='5')
GROUP BY id
ORDER BY timee DESC

This query select all Privatemsgs from the tables, and acting like mail,FOR EX:

此查询从表中选择所有Privatemsgs,并像邮件一样,FOR EX:

If I sent a msg to user b, and user b answered me. I want to display the msg in inbox and outbox of each user.

如果我向用户b发送了一个消息,并且用户b回答了我。我想在每个用户的收件箱和发件箱中显示消息。

A comment to private msg marked as "relatedto" the id of the main msg.

对私人消息的评论标记为“与”相关的主要消息的ID。

The query works, but duplicate the msgs in display (same msg display many times) I tried to do "GROUP BY id" in order to fix it but i got the error:

查询工作,但复制显示中的msgs(相同的msg显示多次)我试图做“GROUP BY id”以修复它,但我收到错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id ORDER BY timee DESC'

THANK YOU!!

1 个解决方案

#1

First, as per PM 77's comment, a union instead of a union all will solve your problem of duplicates. You don't need a group by clause at all.

首先,根据PM 77的评论,工会而不是工会都将解决您的重复问题。您根本不需要group by子句。

Second, you might have a logic error. The top have of your union query has this:

其次,您可能会遇到逻辑错误。你的联合查询的顶级有这个:

FROM privatemsgs 
LEFT JOIN
users AS u ON(privatemsgs.useraid = u.id)
WHERE userbid='5'
AND relatedto=0 and bdel=1)

If any of those fields in the where clause are in the users table, your left join has become an inner join. To keep it as a left join, you have to put all the filters in the join, like this:

如果where子句中的任何字段都在users表中,则左连接已成为内连接。要将其保留为左连接,您必须将所有过滤器放在连接中,如下所示:

FROM privatemsgs 
LEFT JOIN
users AS u ON privatemsgs.useraid = u.id
AND userbid='5'
AND relatedto=0 and bdel=1)

1 个解决方案

#1

更多相关文章

随机推荐