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.

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Apr 07 '20 at 05:13
This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. See [ask] & the voting arrow mouseover texts. Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. — philipxy, Apr 07 '20 at 05:14
In case you don't know: `SELECT * FROM relations GROUP BY uid` is invalid standard SQL since you are selecting using a column that is neither single-valued per group nor aggregated. SQLite happily returns a random `relation` value per group/`uid`. Read an introduction & the manual re functionality you are using. You don't clearly say what output you want in terms of input so we can't know whether this is an error. You say your first query is OK and it randomly takes friend & drops neighbour for Joe so I guess you're happy. — philipxy, Apr 07 '20 at 06:00
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. Isolate the first erroneous subexpression & its input & output. (Debugging fundamental.) — philipxy, Apr 07 '20 at 06:04

Yogesh Sharma · Answer 1 · 2020-04-07T04:00:09.353

You need to filter with on clause :

SELECT id, action, actions.uid, name, relation
FROM actions LEFT JOIN 
     relations 
     USING (uid) AND relation != 'friend' LEFT JOIN -- Don't mix the left & inner join
     users USING (uid);

LEFT JOIN with WHERE clause will turn to INNER JOIN & comparing with null will always false. So, filtered separate while left joining.

However, i would re-express this as :

SELECT a.id, a.action, a.uid, u.name, r.relation
FROM actions a INNER JOIN
     users u
     USING (uid) LEFT JOIN
     relation r
     USING (uid) AND r.relation != 'friend';

score 0 · Accepted Answer · answered Apr 07 '20 at 03:58

0

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

answered Apr 07 '20 at 03:58

Kumar

38
4

This is an extremely poor answer. Please be exact about where this code goes. Please say why it is a answer. Also it is wrong. PS Code questions should be closed & downvoted when they don't have a [mre]. If there was one then you could run your proposed solution & you wouldn't have to make one yourself. Duplicates should also be closed & this is an easily found duplicate. [ask] [answer] [help] – philipxy Apr 07 '20 at 05:25
Thanks! This is the only answer that has exactly solved my problem. The final query would be: SELECT a.id, a.action, a.uid, u.name, coalesce (relation, 'unknown') relantionship FROM actions to INNER JOIN users u USING (uid) LEFT JOIN (SELECT * FROM relations) USING (uid) WHERE relationship! = 'friend' AND relationship! = 'neighbor' GROUP BY a.id – ephramd Apr 07 '20 at 16:28

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

2 Answers2