How to create the inverse where in a sql query-CodePudding

I have a table with the fields FamilyId, Relation, RegId, EmailAddress. Relation can be Head, Partner, Child and Other (always 1 Head). I need 2 sql queries:

1- to determine the number of families with at least 1 email address

2- to determine the number of families with no email addres at all (none of the members)

These two results should add up to the total of the number of families.

As first query I use:

SELECT DISTINCT C1.FamilyId
FROM Family AS C1, Family AS C2
WHERE C1.FamilyId=C2.FamilyId AND C1.Relation="HEAD" AND C2.EmailAddress IS NOT Null;

How to write the inverse where??

CodePudding user response：

If your query is giving you correct results for the first question then you can use NOT IN:

SELECT DISTINCT FamilyId
FROM Family
Where FamilyId NOT IN(
SELECT DISTINCT C1.FamilyId
FROM Family AS C1, Family AS C2
WHERE C1.FamilyId=C2.FamilyId AND C1.Relation="HEAD" AND C2.EmailAddress IS NOT Null);

Note that you should be using the modern join syntax INNER JOIN instead of FROM Family AS C1, Family AS C2.

CodePudding user response：

Try this and let me know if it solve your problem:

SELECT FamilyId
FROM Family
WHERE EmailAddress IS NOT NULL AND Relation <> "HEAD"
Group By FamilyId
HAVING Count(DISTINCT EmailAdress) > 0

CodePudding user response：

There are several ways to make the query, but one way is using EXISTS, for which the inverse is NOT EXISTS.

As you need the number of families, you'd need to return a count.

So the first query would be:

SELECT COUNT(*)
FROM   Family AS C1
WHERE  C1.Relation = 'HEAD'
AND    EXISTS (SELECT 1 
               FROM  Family AS C2
               WHERE C1.FamilyId = C2.FamilyId 
               AND   C2.EmailAddress IS NOT NULL)

Change to NOT EXISTS for the second query.