[SOLVED] Change value within COALESCE within SELECT in MYSQL query based on another field

Change value within COALESCE within SELECT in MYSQL query based on another field

I am working on a complex MYSQL query that performs a UNION of SELECT statements on tables (that each contain joins to a users table). For the UNION to work, the number of fields returned from each SELECT STATEMENT must be the same. My task is to replace a user's name with "Deactivated" when the user is flagged as deactivated.

Simplifed the tables are:

users
id|first|last|uname|deactivated
    
posts
id|post|userid
    
comments
id|comment|postid|userid

The query (simplified) that I am starting with is

SELECT 
  p.post, 
  COALESCE(u.first, u.last, u.uname) as person 
FROM 
  posts `p` 
  LEFT JOIN users `u` ON p.userid = u.id 
WHERE 
  post LIKE '%$querystring%' 
GROUP BY 
  p.id 
UNION 
SELECT 
  c.comment, 
  COALESCE(u.first, u.last, u.uname) as person 
FROM 
  comments `c` 
  LEFT JOIN users `u` ON c.userid = u.id 
  LEFT JOIN posts `p` ON p.id = c.postid 
WHERE 
  comment LIKE '%$querystring%' 
GROUP BY 
  c.id

One option would be to pull the deactivated field with each query and then do logic after the results are returned but it would be nicer to do the logic in the query if possible.

It seems like there ought to be a way to condition the value of person from the users table on whether the deactivated field is true but my knowledge of SQL is not adequate to figuring this out.

How can I change the value returned for person based on the value in the deactivated field in users?

Solution

You can use IF function:

IF(u.deactivated, 'Deactivated', COALESCE(u.first,u.last,u.uname)) as person