MySQL LEFT JOIN 与 WHERE

发布于 2024-10-26 19:00:42 字数 289 浏览 5 评论 0原文

我有两张表公司和流程。公司有字段company_id等。流程有字段company_id和status。我只想要公司表的所有字段，而不需要流程表中状态为 0 的字段。到目前为止，我已经得到了以下内容，但这给了我公司的所有值，即使流程表中状态为 0 的字段也是如此。

SELECT  c.company_id
FROM    company c
LEFT JOIN 
   process p 
ON     c.company_id = p.company_id 
   AND p.status != '0'

原文

Ive got two tables company and process. Company has field company_id and others. Process has fields company_id and status. I want all the fields of the company table only without those in the process table that have a status 0. Ive come sofar with the following but that gives me all the values of company even with the one of process table that has status 0.

SELECT  c.company_id
FROM    company c
LEFT JOIN 
   process p 
ON     c.company_id = p.company_id 
   AND p.status != '0'

分享到QQ

分享到微博

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

发布评论

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

怪异←思 2024-11-02 19:00:42

SELECT c.company_id 
FROM company c 
  LEFT JOIN process p 
    ON c.company_id = p.company_id 
WHERE p.status != '0'
  OR p.status IS NULL
;

第二个解决方案（编辑和简化）：
这看起来不像“黑客”，是吗？

SELECT c.company_id 
FROM company c 
WHERE c.company_id NOT IN
  ( SELECT company_id
    FROM process
    WHERE status = '0'
  )
;

第二种解决方案的问题是，如果您希望显示表 process 中的字段，则需要再进行一次联接（此查询使用 process）。

在第一个解决方案中，您可以安全地将第一行更改为 SELECT c.company_id, c.company_title, p.status 或 , SELECT c.*, p.*。

SELECT c.company_id 
FROM company c 
  LEFT JOIN process p 
    ON c.company_id = p.company_id 
WHERE p.status != '0'
  OR p.status IS NULL
;

2nd solution (edited and simplified):
This doesn't look like "hack", does it?

SELECT c.company_id 
FROM company c 
WHERE c.company_id NOT IN
  ( SELECT company_id
    FROM process
    WHERE status = '0'
  )
;

The problem with second solution is that if you want fields from table process to be shown, one more join will be needed (this query with process).

In the first solution, you can safely change first line to SELECT c.company_id, c.company_title, p.status or , SELECT c.*, p.*.

回复收藏 0 原文