无法在查询中获取正确的记录 - php mysql

发布于 2024-12-09 20:56:50 字数 740 浏览 1 评论 0原文

我正在运行以下查询。

SELECT * 
  FROM exam, result 
 WHERE exam.home = result.external_id 
   AND exam.away != result.external_id;

exam 表中的记录：

home   away
51156   8
51158   7
51158  51182

result 表中的记录：

external_id
51156
51158
51182

在正常情况下，应该获取前两条记录。现在，当我独立使用“！=”时，它执行时不会出现问题。意思是，当我执行

SELECT * FROM exam, result WHERE exam.away != result.external_id

它时，它会正确执行。这就是结果。

home   away
51156   8
51158   7

然而，在我上面提到的错误查询中，输出如下：

id  home   away
111 51156   8
100 51158   7
123 51158  51182

最后一行被考虑，但不应该被计算在内。谁能告诉我为什么？或者有什么替代查询吗？

原文

I am running the following query.

SELECT * 
  FROM exam, result 
 WHERE exam.home = result.external_id 
   AND exam.away != result.external_id;

Records in the exam table:

home   away
51156   8
51158   7
51158  51182

Records in result table:

external_id
51156
51158
51182

In normal scenario, the first two records should be fetched. Now when I use the '!=' independently, it executes without an issue. Means, when I execute

SELECT * FROM exam, result WHERE exam.away != result.external_id

it is executed correctly. This is the result.

home   away
51156   8
51158   7

However, in my above mentioned erroneous query, the output is as follows:

id  home   away
111 51156   8
100 51158   7
123 51158  51182

The last row it is considered though it should not be counted. Can anyone tell me why? Or any alternative for the query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绅士风度i 2024-12-16 20:56:50

据我了解，您正在查找表 result 中存在的 away 列上没有 id 的记录。

如果是这种情况，那么这个查询将产生您想要的结果：

SELECT *
  FROM exam
 WHERE home IN (SELECT external_id FROM result)
   AND away NOT IN (SELECT external_id FROM result)

To my understanding you are looking for records which do not have an id on the column away that is present in the table result.

If that is the case, then this query will produce the result you want:

SELECT *
  FROM exam
 WHERE home IN (SELECT external_id FROM result)
   AND away NOT IN (SELECT external_id FROM result)

回复收藏 0 原文

迷雾森÷林ヴ 2024-12-16 20:56:50

试试这个：

SELECT e.home,e.away,r.external_id
FROM exam e join result r on r.external_id = e.home and e.away != r.external_id 
where e.away not in (select r.external_id from result r)

try this:

SELECT e.home,e.away,r.external_id
FROM exam e join result r on r.external_id = e.home and e.away != r.external_id 
where e.away not in (select r.external_id from result r)

回复收藏 0 原文

野味少女 2024-12-16 20:56:50

SELECT *  
  FROM exam  
 WHERE home IN (SELECT external_id FROM result WHERE exam.home = result.external_id)  
   AND away IN (SELECT external_id FROM result WHERE exam.away!= result.external_id )

SELECT *  
  FROM exam  
 WHERE home IN (SELECT external_id FROM result WHERE exam.home = result.external_id)  
   AND away IN (SELECT external_id FROM result WHERE exam.away!= result.external_id )

回复收藏 0 原文

~没有更多了~