Find all rows where a column is not equal to a specific value

I have the below table

I want to write a query to pull the id's that do not have fid = 3797?

Is this possible?

I can query for ids where it has fid=3797 but the val is NULL, but struggling to get a query to get the ids where it is missing the fid=3797 field all together.

Answers

Yes, it's possible to write a query to pull the IDs that do not have fid = 3797. You can achieve this using a NOT EXISTS clause or a LEFT JOIN with a condition that checks for the absence of matching rows in the table where fid = 3797. Here's how you can do it with both approaches:

Using NOT EXISTS:

SELECT id
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t1.id = t2.id
    AND t2.fid = 3797
);

Using LEFT JOIN:

SELECT t1.id
FROM your_table t1
LEFT JOIN your_table t2 ON t1.id = t2.id AND t2.fid = 3797
WHERE t2.id IS NULL;

Both queries will give you the IDs from the table where fid is not equal to 3797. Choose the one that suits your preferences or performs better in your environment.