douou1872
2012-07-26 15:52
浏览 99
已采纳

如何使用PHP / mySQL查询一个表中存在的条目而不是另一个表中的条目?

I want to query only the entries in table2 which contains a "course" value and "course" does not exist in table1. I initially inner joined table1 to table2 based on the "course" value. Here's what I have so far, which doesn't work:

    $query = "SELECT value1,value2,value3 FROM table2 INNER JOIN table1 USING(course)
            WHERE table2.sem = '$semester' AND NOT EXISTS (SELECT course FROM table1)
            ORDER BY course";
    $result = mysql_query($query2) or die(mysql_error());

    while ($row2 = mysql_fetch_array($result))
    {
        print_r($row);echo "<br><br>";
    }

This inner join code works:

    $query = "SELECT * FROM table1 INNER JOIN table2 USING(course) 
            WHERE table1.sem = '$semester'
            ORDER BY course";
    $result = mysql_query($query) or die(mysql_error());

图片转代码服务由CSDN问答提供 功能建议

我想只查询table2中包含“course”值的条目,而“course”不存在于 表格1。 我最初基于“课程”值将table1连接到table2。 这是我到目前为止所做的,它不起作用:

  $ query =“SELECT value1,value2,value3 FROM table2 INNER JOIN table1 USING(course)
 WHERE table2  .sem ='$ semester'AND NOT EXISTS(SELECT course FROM table1)
 ORDER BY course“; 
 $ result = mysql_query($ query2)or die(mysql_error()); 
 
 while($ row2 =  mysql_fetch_array($ result))
 {
 print_r($ row); echo“&lt; br&gt;&lt; br&gt;”; 
} 
   
 
 

这 内连接代码有效:

  $ query =“SELECT * FROM table1 INNER JOIN table2 USING(course)
 WHERE table1.sem ='$ semester'
 ORDER BY course  “; 
 $ result = mysql_query($ query)或die(mysql_error()); 
   
 
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douan7601 2012-07-26 15:55
    已采纳
    $query = "SELECT value1,value2,value3 FROM table2 
                WHERE table2.sem = '$semester' AND course IS NOT NULL
                AND course NOT IN(SELECT course FROM table1)
                ORDER BY course";
    

    EDIT: If you want an in depth explanation as to why to go this route instead of the left join (which will also work) check out this article:

    http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题