MySQL query problem.

Question

I'm trying to just display articles and their comments where users deletion field is not 1 and active is NULL, but for some reason my query displays comments from articles where the deletion field is 1. Can someone help me fix this?

Sorry for all the info but I have been stuck on this for about 3 days and really wont to solve this problem ASAP.

MySQL tables

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 tables input

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 tables input

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

users tables input

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

My Current Display Output

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

My Desired Output

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

My current MySQL code.

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

try this

EDIT :

   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;

Note : its not advisable to select all (*) with joined tables.