请考虑以下模式：

CREATE TABLE users 
(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
);

CREATE TABLE mashup_x 
(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    date VARCHAR(255),
    warranty VARCHAR(30) NOT NULL,
    userid VARCHAR(30) NOT NULL,
    mainthread VARCHAR(50),
    certified VARCHAR(50)
);

INSERT INTO users VALUES (1, "John");
INSERT INTO users VALUES (2, "Adam");

INSERT INTO mashup_x
VALUES (1, 1540804136000, 1, 2, 1, 0); /*this is a main entry with sub entries below*/

INSERT INTO mashup_x
VALUES (2, 1540804256000, 1, 1, 1, 1);

INSERT INTO mashup_x
VALUES (3, 1540804256000, 1, 1, 1, 1);

INSERT INTO mashup_x
VALUES (4, 1540804136000, 1, 2, 4, 1); /*this is a main entry with NO sub entry below*/

尝试的查询：

select 
    mashup_x.id as ok, users.name, mashup_x.certified, mashup_x.mainthread 
from 
    mashup_x, users 
where 
    users.id = mashup_x.userid 
    and certified = 1 
    and mashup_x.id != mainthread 
    and (select certified from mashup_x 
         where id = mainthread and id = mashup_x.id and certified = 1) = 1
order by 
    mashup_x.date desc

http://sqlfiddle.com/#!9/0f3537/1号楼
如果主条目未经认证，则不应显示子条目，您如何知道它们是子条目？您可以在子条目的mainthread列中看到，子条目被分配了maintry的id。
当前它选择任何主条目，即使我只选择了一个id与子条目主线程相同的条目
正如你所看到的，主条目1没有被认证，但是主条目4（没有子条目）被认证，它仍然显示主条目1的子条目。我做错什么了？