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

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.

CodePudding user response：

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

CodePudding user response：

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