Mysql – Group by multiple column acquired from join

group byMySQL

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

Best Answer

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;