CODEWITHSUNDEEP
sqlsql-server
mikec
mikec

Reputation: 165

SQL : How to SELECT a record where certain attributes are null

I am using SQL Server 2014 database, and SQL Server Management Studio to create and run queries.

Tables are :

Persons

| ID | personName |
+----+------------+
| 1  |    Hamish  |
| 2  |    Morag   |
| 3  |    Ewan    |

Cars

| ID | CarName |
+----+---------+
| 1  |  Humber |
| 2  |  Austen |
| 3  |  Morris |

Gadgets

| ID | GadgetName |
+----+------------+
| 1  |  Cassette  |
| 2  |     CD     |
| 3  |   Radio    |

CarToPersonMap

| ID | CarID | PersonID |
+----+-------+----------+
| 1  |   1   |    1     |
| 2  |   2   |    1     |
| 3  |   3   |    2     |

CarToGadgetMap

| ID | CarID | GadgetID |
+----+-------+----------+
| 1  |   2   |    2     |

The map tables have the appropriate foreign keys.

I want to find people who have a Cars but none of the Cars have gadgets. So in the example above I want to find Morag who has a Car with no Gadgets. Hamish has 2 Cars but 1 one the Cars has a gadget so I do not want the ResultSet to include Hamish.

Upvotes: 0

Views: 711

Answers (4)

Charlieface
Charlieface

Reputation: 72415

You can do conditional aggregation inside an EXISTS

SELECT personName
FROM Persons p 
WHERE EXISTS (SELECT 1
    FROM CarToPersonMap cpm
    LEFT JOIN CarToGadgetMap cgm ON cpm.carId = cgm.carID
    WHERE cpm.personId = p.ID
    HAVING COUNT(cgm.carID) = 0
);

What this says is:

  • Get all Persons that have rows in the subquery.
  • That subquery is all of their CarToPersonMap, left joined with relevant CarToGadgetMap.
  • There must be no rows which have a matching CarToGadgetMap value (because COUNT only counts non-nulls).

db<>fiddle

Upvotes: 0

an33sh
an33sh

Reputation: 1134

You can use EXISTS also to get the expected output. The query will be something like,

SELECT DISTINCT personName
FROM Persons p 
WHERE 
    EXISTS ( SELECT 1 FROM CarToPersonMap WHERE  personId = p.ID )
    AND NOT EXISTS ( SELECT 1 
            FROM CarToGadgetMap cgm 
            JOIN CarToPersonMap cpm ON cpm.personId = p.ID AND cpm.carId = cgm.carId)

here is the fiddle

Upvotes: 0

Andrew Corrigan
Andrew Corrigan

Reputation: 1057

I'd use a couple of CTEs:

With CarGadgetCount AS (
         SELECT c.ID as CarID, 
                SUM(CASE cg.ID IS NOT NULL THEN 1 ELSE 0 END) AS GadgetCount
         FROM Car c 
              LEFT JOIN CarToGadgetMap cg
                        ON cg.CarID = c.ID
         GROUP BY c.ID
    ),
    PersonCarCount AS (
         SELECT p.ID as PersonID, 
                SUM(CASE cp.ID IS NOT NULL THEN 1 ELSE 0 END) AS CarCount
         FROM Person p 
              LEFT JOIN CarToPersonMap cp
                        ON cp.PersonID = p.ID
    )
    PersonGadgetCount AS (
         SELECT p.ID AS PersonID,
                SUM(CASE WHEN cg.GadgetCount IS NULL THEN 0 ELSE cg.GadgetCount END) as GadgetCount
         FROM Person p
              LEFT JOIN CarToPersonMap cp
                        ON cp.PersonID = p.ID
              LEFT JOIN CarGadgetCount cg 
                         ON cg.CarID = cp.CarID
         GROUP BY p.ID
    )
SELECT p.personName 
FROM Person p
     INNER JOIN PersonGadgetCount pg
                ON pg.PersonID = p.ID
     INNER JOIN PersonCarCount pc 
                ON pc.PersonID = p.ID
WHERE pc.CarCount > 0
      AND pg.GadgetCount = 0;

That way, if you want to actually see who has cars and who has gadgets, then you can switch the WHERE clause and specified columns in the select

Upvotes: 0

LukStorms
LukStorms

Reputation: 29677

Left join the CarToGadgetMap to the CarToPersonMap.

Then group by the person.

Those persons will be having more than 0 cars but 0 gadgets.

select PersonName
from Persons as Person
left join CarToPersonMap as CarPerson
  on CarPerson.PersonId = Person.ID
left join CarToGadgetMap as CarGadget
  on CarGadget.carId = CarPerson.CarId
group by Person.ID, PersonName
having count(CarPerson.CarID) > 0    -- has car(s)
   and count(CarGadget.GadgetID) = 0 -- no gadgets
PersonName
Morag

Demo on db<>fiddle here

Upvotes: 2

Related Questions