1

this is part of a query result :

enter image description here

all three columns are user_ids acquired from multiple tables join .
as you see each row has only one user_id and two other user_ids are necessarily null . I'm going to group this rows by user_id regardless of the column it is on.

so i need to have all user_ids in one column (instead of three) to make me enable to perform group by on it. the ultimate result should be like this:

enter image description here

1

You can do this using COALESCE. If table1.user_id is null then it will use table2.user_id. If that is null then it uses table3.user_id and so on.

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`

You won't be able to use the derived xx_user_id column in a WHERE clause however you can use it in a HAVING clause

SELECT COALESCE(table1.user_id, table2.user_id, table3.user_id) AS `xx_user_id`
FROM ...table joins...
GROUP BY `xx_user_id`
HAVING `xx_user_id` = 123;
  • excellent . and this is the link to MYSQL COALESCE documentation – alex Jun 26 '16 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.