Why does a WHERE condition discard content obtained by LEFT JOIN? SQLITE

Question

Example of 3 related tables:

• User 2 (Marie) isn't registered in the "relations" table. Therefore later when using a SELECT its relation value must be null / empty.

I make the following SELECT that gets all the related data that interests me (correctly):

SELECT id, action, actions.uid, name, relation
FROM actions
LEFT JOIN (SELECT * FROM relations GROUP BY uid) USING (uid)
INNER JOIN users USING (uid)
GROUP BY actions.id;

And return:

The problem occurs when I need to use conditions to "drop" results based on the relationship type.

I make the following query to discard "friend" (relation) from the previous results:

SELECT id, action, actions.uid, name, relation
FROM actions
LEFT JOIN (SELECT * FROM relations GROUP BY uid) USING (uid)
INNER JOIN users USING (uid)
WHERE relation != 'friend'
GROUP BY actions.id;

However it should return one result (Marie) and it returns 0 results ...

I'm not interested in getting Marie as a result of a type condition like '...WHERE relation IS NULL', my queries should work by discard since they belong to a filter tool.

Answer 1

Try using coalesce(Relation,'') != 'friend'.Hope it works