MySQL查询:展示用户`deletion`字段不为`1`，`active`为` `NULL` `的文章及其评论

Question

我试图只显示users deletion字段不是1且active为NULL的文章及其评论，但是由于某种原因，我的查询显示了deletion字段为1的文章的评论。我该如何解决这个问题？

MySQL表

CREATE TABLE users (
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(255) NULL,
    password CHAR(128) NOT NULL,
    active CHAR(32),
    deletion TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id),
    UNIQUE KEY (username)
);

CREATE TABLE articles_comments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_comment_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    article_id INT UNSIGNED NOT NULL,
    comment TEXT NOT NULL,
    date_created DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY user_id (user_id),
    KEY article_id (article_id)
);

CREATE TABLE users_articles (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    title TEXT NOT NULL,
    summary TEXT DEFAULT NULL,
    content LONGTEXT NOT NULL,
    PRIMARY KEY (id)
);

articles_comments表输入

comment_id  parent_comment_id   user_id     article_id  comment
1           0                   1           1           -
2           0                   2           1           -
3           0                   2           2           -
4           0                   1           2           -
5           0                   2           3           -
6           0                   2           4           -
7           1                   2           1           -
8           2                   2           1           -
9           0                   3           1           -

users_articles表输入

id  user_id     title   summary     content
1   3           -       -           -
2   4           -       -           -
3   4           -       -           -
4   4           -       -           -

用户表输入

user_id     username    password    deletion    active
1           -           -           0           NULL
2           -           -           0           NULL
3           -           -           1           NULL
4           -           -           0           NULL

我的当前显示输出

user_id     comment_id      article_id 
1           1               1
2           2               1
2           3               2
1           4               2
2           5               3
2           6               4
2           7               1
2           8               1

我想要的输出

user_id     comment_id      article_id 
2           3               2
1           4               2
2           5               3
2           6               4

我当前的MySQL代码。

SELECT *
  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

Answer 1

尝尝这个

编辑:

   SELECT * FROM users_articles,articles_comments,users where users_articles.id = articles_comments.article_id and articles_comments.user_id = users.user_id and users_articles.user_id=users.user_id and userusers.active IS NULL AND users.deletion = 0;

注意:不建议选择所有(*) with joined表。