MySQL query problem.

Question

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

Answer 1

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