Conditional select value based on existence in another table?

Question

I have a table of users, and a table that keeps track of threads and the users that are involved in each thread. I am trying to be able to craft a SELECT statement that I can look at any thread, and get back the full list of users along with a column of whether or not they are present in it. Initially and naively I used the following to start:

SELECT 
listusers.idx as Value,
CONCAT(FirstName, ' ', LastName) as Label,
False as selected
FROM
listUsers
WHERE 
listusers.customerId = 0

This just gets me back all of the users and at this point I programatically looked to the other table and manually switched on the users that are in it.

My attempt at doing this via a query is the following -

SELECT l.idx as Value,
CONCAT(FirstName, ' ', LastName) as Label,
IF(h.userId IS NULL,False, True) as Selected
FROM listusers l
LEFT JOIN helpmessageparticipants h ON l.idx = h.userId
WHERE l.customerId = 0 AND 
h.parentThreadId={Root Container.threadId})

But for some reason that only returns the users that ARE selected. If only 3 users are in the thread, I only get those three back and all are True in the selected. What I want, end result, is to always get back all the users (the users where l.customerId=0) and and additional column of whether or not they are present in helpmessageparticipants for a certain thread as a true or false boolean column. Whats wrong with my second query?

I am using MySQL 5.6.

the table which is LEFT JOINed in this case helpmessageparticipants can't have non IS NULL filter check conditions which h.parentThreadId = value is otherwise it will filter as a INNER JOIN.. Very common MySQL error it would be ok if the condition was h.parentThreadId IS NULL — Raymond Nijland
– Raymond Nijland, Commented Apr 25, 2019 at 13:57

Nick · Accepted Answer · 2019-04-25 13:52:33Z

2

You need to move the

h.parentThreadId={Root Container.threadId})

condition out of the WHERE clause and into the join condition, otherwise it converts your LEFT JOIN into an INNER JOIN.

SELECT l.idx as Value,
CONCAT(FirstName, ' ', LastName) as Label,
IF(h.userId IS NULL,False, True) as Selected
FROM listusers l
LEFT JOIN helpmessageparticipants h ON l.idx = h.userId AND 
       h.parentThreadId={Root Container.threadId})
WHERE l.customerId = 0

This is discussed in the manual under 'Outer Join Optimization':

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to an inner join

In your example, h.parentThreadId={Root Container.threadId}) will be false for a generated NULL row, thus the query changes to an INNER JOIN and you only get back the users that are selected.

answered Apr 25, 2019 at 13:52

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Nijland Over a year ago

"This is discussed in the manual under 'Outer Join Optimization':" Yes iám not sure it's this is MySQL specific only or if other databases system also do this as the sql tag is also tagged in this question.

Nick Over a year ago

@RaymondNijland This isn't specific to MySQL, it's how LEFT JOIN works in general. Note that adding OR h.parentThreadId IS NULL would also have resolved the problem. See dbfiddle.uk/…

Gordon Linoff · Accepted Answer · 2019-04-25 14:17:45Z

The LEFT JOIN version requires that the condition be in the ON clause rather than the WHERE clause, as Nick points out. I would write this as:

SELECT l.idx as Value,
       CONCAT(FirstName, ' ', LastName) as Label,
       (h.userId IS NOT NULL) as Selected
FROM listusers l LEFT JOIN
     helpmessageparticipants h
     ON l.idx = h.userId AND 
        h.parentThreadId = {Root Container.threadId})
WHERE l.customerId = 0;

However, this assumes that there is only one match in h -- otherwise you will get duplicate rows. For a single thread this might not be an issue. But you might have other filtering conditions that allow multiple matches.For this reason, I think an exists clause might be more appropriate.

SELECT l.idx as Value,
       CONCAT(FirstName, ' ', LastName) as Label,
       (EXISTS (SELECT 1
                FROM helpmessageparticipants h
                WHERE l.idx = h.userId AND 
                      h.parentThreadId = {Root Container.threadId}
              )
       ) as Selected
FROM listusers l LEFT JOIN

WHERE l.customerId = 0;

Collectives™ on Stack Overflow

Conditional select value based on existence in another table?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related