CODEWITHSUNDEEP
sqlite
Talen Kylon
Talen Kylon

Reputation: 1968

How can I find records that don't have a specific value in a field?

I didn't create this database so my knowledge of how it's built is very limited, but I've been asked to write a query to get some information. I need to find all of the devices that do not have Microsoft .NET Framework 4 Client Profile installed on them. It's been a little while since the last time I've written some SQL so pardon any rookie syntax mistakes I've made.

I have the following tables: 

devices
software
software_installations

I believe the relationships are using the following fields:

software_installations has a computer_id, and software_id

software has an id

devices has an id

I was able to query all of the devices that do have the software installed on them with the following query:

SELECT devices.name, software.name
FROM software_installations
         INNER JOIN devices ON devices.id = software_installations.computer_id 
         INNER JOIN software ON software.id = software_installations.software_id
WHERE devices.dn LIKE "%OU=POLICE_OU%" AND software.name = "Microsoft .NET Framework 4 Client Profile";

Now onto the information I do need, the one's that don't. My initial thought was to just make the following change, 

SELECT devices.name, software.name
FROM software_installations
         INNER JOIN devices ON devices.id = software_installations.computer_id 
         INNER JOIN software ON software.id = software_installations.software_id
WHERE devices.dn LIKE "%OU=POLICE_OU%" AND software.name != "Microsoft .NET Framework 4 Client Profile";

But upon execution I realized that was not what I needed, so now I am stumped.

Upvotes: 0

Views: 31

Answers (1)

Himanshu
Himanshu

Reputation: 2454

Try EXCEPT operator :-

SELECT devices.name
FROM devices

EXCEPT

SELECT devices.name
FROM software_installations
         INNER JOIN devices ON devices.id = software_installations.computer_id 
         INNER JOIN software ON software.id = software_installations.software_id
WHERE devices.dn LIKE "%OU=POLICE_OU%" AND software.name = "Microsoft .NET Framework 4 Client Profile";

Upvotes: 1

Related Questions