How to get records with 0 relations

Question

0.00/5 (No votes)

See more:

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

SQL

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)

Posted 8-Feb-16 6:28am

Kristian_dk

Updated 8-Feb-16 7:00am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 8-Feb-16 13:05pm

How do you want to join tables, if perOrgRelationId field in Persons table is empty?

PIEBALDconsult 8-Feb-16 15:03pm

Try an OUTER JOIN.

Kristian_dk 8-Feb-16 15:06pm

Sorry i did not think about that :-)

3 solutions

Solution 1

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

SQL

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

SQL

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. :)

Posted 8-Feb-16 7:01am

phil.o

Comments

Kristian_dk 8-Feb-16 15:09pm

Thanks for posting two very cool suggestions. Sorry that my question was a Little unspecific. However, non of your SQL code is working against my server.. hmm thats weird

I get at incorrect syntax near keyword 'GROUP'

Richard Deeming 8-Feb-16 15:15pm

The HAVING clause needs to be after the GROUP BY clause. :)

Kristian_dk 8-Feb-16 15:20pm

Richard that actually helped ;-) great - now it Works!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-02-08T07:13:00

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:

SQL

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.

Richard Deeming · Accepted Answer · 2016-02-08T07:21:00

Solution 3

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

SQL

-- 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
    )
;

Posted 8-Feb-16 7:21am

Richard Deeming

Comments

Kristian_dk 8-Feb-16 15:13pm

Both of these also Works - and i have decided to include both in my report. Really nice to both post the number and the actual list. Can i challenge you a Little bit.

In the Number of organisations solution, what if I would like to have to numbers. to compare in a chart.
1. The total number of organisations
2. The number of organisations with no contact persons related (as you have already solved)
Then i can make a percentage chart of our dataquality.
Can you help me here?

Richard Deeming 8-Feb-16 15:27pm

For that query, a LEFT JOIN will be easier:

SELECT
    Count(DISTINCT O.System_ID) As TotalNumberOfOrganisations,
    Count(DISTINCT P.perOrgRelationID) As TotalWithContacts,
    Count(DISTINCT O.System_ID) - Count(DISTINCT P.perOrgRelationID) As TotalWithoutContacts
FROM
    Organisations As O
    LEFT JOIN Persons As P
    ON P.perOrgRelationId = O.System_ID
;

Richard Deeming 8-Feb-16 15:29pm

I see the phantom down-voter has struck again. Still haven't worked up the courage to explain down-voting an answer which solves the OP's question, eh? :(

Wendelius 9-Feb-16 0:10am

Countered

How to get records with 0 relations

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us