如何获得0关系的记录 [英] How to get records with 0 relations

问题描述

亲爱的朋友们，

我根本无法解决这个谜语。我试图找到我们数据库中的每个公司，没有任何联系。



我有两张桌子：

组织：

 System_ID 
姓名

人员：

 System_ID 
 Full_name 
 perOrgRelationId 

这两个表可以加入 perOrgRelationId 。



我如何通过<$ c计算没有相关人员的组织数量$ c> perOrgRelationId 。



我已经尝试过这么多:-(



我尝试了什么：



这根本不起作用

  SELECT  COUNT（Organisations.System_ID） AS  Numberofcompanies，COUNT（Persons.System_ID） AS  Numberofcontacts，Persons.perOrgRelationId 
  FROM 组织 INNER   JOIN  
人 ON  Organisations.System_ID = Persons.perOrgRelationId 
  HAVING （COUNT（Persons.System_ID）>  0 ）

解决方案

我认为你的结果并不是很清楚你想要，因为你做了两个不相等的陈述。

当你说：

我试图找到我们数据库中的每个公司没有任何联系。

请求是

  SELECT  
 Organisations.System_ID 
，Organisations.Name 
  FROM  
组织
  LEFT   JOIN 人员 ON  Persons.perOrgRelationId = Organisations.System_ID 
  HAVING  
 COUNT（Persons.System_ID）=  0  
  GROUP   BY  
 Organisations.System_ID 

但是当你说

我该如何计算通过perOrgRelationId没有相关人员的组织数量。

请求是

  SELECT  
 COUNT（Organisations.System_ID） AS  NumberOfOrganisations 
  FROM  
组织
  LEFT   JOIN 人 ON  Persons.perOrgRelationId = Organisations.System_ID 
  HAVING  
 COUNT（Persons.System_ID）=  0  
  GROUP   BY  
 Organisations.System_ID 

这些要点很重要：

- 当您使用聚合函数（如 COUNT 或<$ c $）时，您必须指定 GROUP BY 子句c> SUM 。

- 如果你想要那些没有任何人相关的组织，你必须使用 LEFT JOIN 。 INNER JOIN 只返回两个表中都出现的记录。

- 如果你想要没有任何人相关的组织，为什么测试计数大于零？



希望这会有所帮助。 ：）

请阅读我对该问题的评论。



我建议阅读： SQL连接的可视化表示 [ ^ ]



下面查询将返回没有联系人的组织列表：

  SELECT  O.System_ID，COUNT（P .perOrgRlationId） AS  NumberOfContacts 
  FROM 组织 AS  O  LEFT   JOIN 人员 AS  P  ON  O.System_ID = P.perOrgRelationId 
  WHERE  P.perOrgRelationId  IS   NULL  
  GROUP   BY  O.System_ID 

随意根据需要进行更改。

一个 NOT EXISTS 查询可能比 LEFT JOIN 等价物更容易理解。根据这篇文章 [ ^ ]，甚至可能表现更好。

   -   组织数目： 
  SELECT  
计数（*）
  FROM  
组织 As  O 
  WHERE  
 不 存在 
（
  SELECT  * 
  FROM 人员作为 P 
  WHERE  P.perOrgRelationId = O.System_ID 
）
; 
 
   -   组织清单： 
  SELECT  
 System_ID，
名称
  FROM  
组织 As  O 
  WHERE  
 不 存在 
（
  SELECT  * 
  FROM 人员作为 P 
  WHERE  P.perOrgRelationId = O .System_ID 
）
; 

Dear freinds,
I simply can't resolve this riddle. Im trying to find every Company in our database without any contacts.

I have two tables:
Organisations:

System_ID
Name

Persons:

System_ID
Full_name
perOrgRelationId

The two tables can be joined on perOrgRelationId.

How on earth do I Count the number of Organisations that has no persons related via the perOrgRelationId.

I have tried so much :-(

What I have tried:

This does not Work at all

SELECT        COUNT(Organisations.System_ID) AS Numberofcompanies, COUNT(Persons.System_ID) AS Numberofcontacts, Persons.perOrgRelationId
FROM            Organisations INNER JOIN
                         Persons ON Organisations.System_ID = Persons.perOrgRelationId
HAVING        (COUNT(Persons.System_ID) > 0)

解决方案

I think it's not really clear for you which result you want, because you made two statements that are not equal.
When you say :

Im trying to find every Company in our database without any contacts.

the request is

SELECT
  Organisations.System_ID
 ,Organisations.Name
FROM
  Organisations
  LEFT JOIN Persons ON Persons.perOrgRelationId = Organisations.System_ID
HAVING
  COUNT(Persons.System_ID) = 0
GROUP BY
  Organisations.System_ID

But when you say

How on earth do I Count the number of Organisations that has no persons related via the perOrgRelationId.

the request is

SELECT
  COUNT(Organisations.System_ID) AS NumberOfOrganisations
FROM
  Organisations
  LEFT JOIN Persons ON Persons.perOrgRelationId = Organisations.System_ID
HAVING
  COUNT(Persons.System_ID) = 0
GROUP BY
  Organisations.System_ID

These points are important:
- you have to specify a GROUP BY clause when you use aggregation functions like COUNT or SUM.
- if you want the organisations which haven't got anybody related, you have to use a LEFT JOIN. An INNER JOIN will only return the records which have occurrences in both tables.
- if you want the organisations which haven't got anyone related, why testing for a count greater than zero?

Hope this helps. :)

Please, read my comment to the question.

I'd recommend to read this: Visual Representation of SQL Joins[^]

Below query will return the list of organizations having none of contacts:

SELECT O.System_ID, COUNT(P.perOrgRlationId) AS NumberOfContacts
FROM Organisations AS O LEFT JOIN Persons AS P ON O.System_ID = P.perOrgRelationId
WHERE P.perOrgRelationId IS NULL
GROUP BY O.System_ID

Feel free to change it to your needs.

A NOT EXISTS query will probably be easier to follow than the LEFT JOIN equivalent. According to this article[^], it might even perform better.

-- Number of organisations:
SELECT
    Count(*)
FROM
    Organisations As O
WHERE
    Not Exists
    (
        SELECT *
        FROM Persons As P
        WHERE P.perOrgRelationId = O.System_ID
    )
;

-- List of organisations:
SELECT
    System_ID,
    Name
FROM
    Organisations As O
WHERE
    Not Exists
    (
        SELECT *
        FROM Persons As P
        WHERE P.perOrgRelationId = O.System_ID
    )
;

这篇关于如何获得0关系的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！