开发者

MySQL query problem

How can I add the following code example 1 to example 2 without messing up my query.

Example 1

INNER JOIN users ON users_articles.user_id = users.user_id

Example 2.

SELECT users.*
FROM users_articles
INNER JOIN articles_comments ON users_articles.id = articles_comments.article_id
INNER JOIN users 开发者_运维技巧ON articles_comments.user_id = users.user_id
WHERE users.active IS NULL
AND users.deletion = 0
ORDER BY articles_comments.date_created DESC
LIMIT 50


If I understand you correctly, you want to join table users twice, once for comments, and once for articles? In that case, you need to alias the table. I usually use single- or two-letter aliases for brevity even when I do not double tables, but it is not important.

SELECT ...
FROM users_articles UA
INNER JOIN articles_comments AC ON UA.id = AC.article_id
INNER JOIN users UC ON AC.user_id = UC.user_id
  AND UC.active IS NULL
  AND UC.deletion = 0
INNER JOIN users UA ON UA.user_id = users.user_id
  AND UA.active IS NULL
  AND UA.deletion = 0
ORDER BY AC.date_created DESC
LIMIT 50

BTW, Don't use SELECT *, it is almost always better to list specifically what you want.

Disclaimer: I might have misunderstood what you are trying to do; posting a bit of context to your code is usually a good idea. In this case, the table names threw me a bit (if it's what I think it is, I'd've just gone with users, articles and comments).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜