如何根据mysql中另一个表的结果从一个表中获取内容 - how to fetch content from one table according to results from another table in mysql

how to fetch content from one table according to results from another table in mysql

我有两个mysql表，其中一个表的内容叫做请愿

{"success":1,"petitions":[{"id":"6","name":"should he go","timestamp":"2013-10-26 03:02:44"},{"id":"3","name":"Olara Otunu should get married","timestamp":"2013-10-24 14:33:53"},{"id":"4","name":"Teachers deserve 30 not 20 salary rise","timestamp":"2013-10-24 14:33:53"},{"id":"5","name":"Prostitution should be banned","timestamp":"2013-10-24 14:33:53"},{"id":"1","name":"Has Jennifer Musisi done great work for Kampala","timestamp":"2013-10-24 14:32:58"},{"id":"2","name":"Do lecturers deserve 100% salary increase","timestamp":"2013-10-24 14:32:58"}]}

另一个表称为请愿响应

{"success":1,"petition_response":[{"id":"2","petitionID":"2","yes":"0","no":"1","memberID":"14","timestamp":"2013-11-02 08:36:20"},{"id":"1","petitionID":"1","yes":"1","no":"0","memberID":"14","timestamp":"2013-11-01 21:26:02"}]}

我需要选择请愿书中没有回应的请愿书。但是，如果他们在petition_response表中有任何响应，则memberID不应等于14

我试过下面的代码，但它不工作

$result = mysql_query("select * from petition_response where memberID='14' order by timestamp DESC", $db->connect());
while($row = mysql_fetch_assoc($result)){
    $id = $row['id'];
    $result2 = mysql_query("select * from petitions where id != '$id' order by timestamp DESC", $db->connect());            
}
return $result2;

您希望实现反联接，MySQL中有三种可能性：

使用NOT IN:

SELECT *
FROM   petitions
WHERE  id NOT IN (
         SELECT petitionID
         FROM   petition_response
         WHERE  memberID = 14
       )

使用NOT EXISTS:

SELECT *
FROM   petitions p
WHERE  NOT EXISTS (
         SELECT *
         FROM   petition_response r
         WHERE  r.petitionID = p.id
            AND r.memberID   = 14
         LIMIT  1
       )

使用OUTER JOIN:

SELECT p.*
FROM   petitions p
         LEFT OUTER JOIN petition_response r
                      ON r.petitionID = p.id AND r.memberID = 14
WHERE  r.petitionID IS NULL

请在sqlfiddle上查看它们。

根据@Quassnoi的分析：

摘要

MySQL可以优化这三种方法来做一种NESTED LOOPS ANTI JOIN。

它将从t_left中获取每个值，并在t_right.value的索引中查找它。在索引命中或索引未命中的情况下，相应的谓词将立即分别返回FALSE或TRUE，并且将立即决定是否从t_left返回该行，而不检查t_right中的其他行。

然而，这三种方法生成三个不同的计划，由三段不同的代码执行。执行EXISTS谓词的代码比执行优化为使用Not exists方法的index_subquery和LEFT JOIN的代码效率低30%。

这就是为什么在MySQL中搜索缺失值的最佳方法是使用LEFT JOIN / IS NULL或NOT IN而不是NOT EXISTS。

$query = "
SELECT * 
  FROM petition_response pr
  LEFT
  JOIN petitions p
    ON p.id = pr.id
 WHERE pr.memberID = 14 
   AND p.id IS NULL
 ORDER
    BY pr.timestamp DESC;
";