CODEWITHSUNDEEP
sqlite
Tango
Tango

Reputation: 669

JOINing Tables To Find Absent Values In a Column

I have two tables, Properties, and Comments. (It's a listing of lots we're looking at to evaluate for potential investments.) In Properties I have the column 'Idx' which is unique and the value in Idx is used to identify the particular property in other tables.

In Comments I have these columns:

LotIndex (which is the same as Idx in Properties) Name Rating Comments

In addition to the previous use of the Comments table, where we all had our comments stored, I'm adding a user, "Auto," so I can auto-score each lot on a number of points. Now that I have this, I'd like my scoring program to be able to go through and find only the properties that do not have any entry in Comments with Name = 'Auto'. (I do not care if they have entries in Comments where Name has other values.)

I know if all I want to do is find properties without any entry in Comments without a value in Rating, I can do something like this:

SELECT P.Idx FROM Properties as P LEFT JOIN Comments AS C WHERE P.Idx = C.LotIndex WHERE C.Rating = '' OR C.Rating = '0'

But how do I use a JOIN to find a list of values in P.Idx where there may be entries and Names, Ratings in Comments by multiple people when all I want is a list of values in P.Idx where there is no corresponding entry in Comments with the Name being Auto?

Upvotes: 1

Views: 40

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169384

This is a perfect situation for a correlated subquery, e.g.:

SELECT P.Idx 
FROM Properties as P 
WHERE NOT EXISTS (
    SELECT 1 
    FROM Comments AS C 
    WHERE P.Idx = C.LotIndex AND C."name" = 'Auto');

Upvotes: 2

Related Questions