Question 20119 in Mysql Question posted in
The official MySQL documentation can be found here.

Want distinct list of ids in mysql variable

Table
|id |user_id | req_user_id |
|---|--------|-------------|
|1  | 1      |   2         |
|2  | 2      |  9          | 
|3  | 7      |   2         |

When I search with user_id or req_user_id = 2 output should be (1,9,7,2)
tried

SET @list_req = (SELECT GROUP_CONCAT(DISTINCT CONCAT(user_id,',',req_user_id)) FROM send_request WHERE req_user_id= 2 or user_id=2);

output should be (1,9,7,2)

Tags:

2

Answers


  1. 0

    You can do:

    select group_concat(id separator ',')
from (
  select user_id as id from t
  union 
  select req_user_id from t
) x

    Result:

    group_concat(id separator ',')
------------------------------
1,2,7,9

    See running example at db<>fiddle.

    Login or Signup to reply.
  2. 0

    I see that @The Impaler’s answer is great.

    This is an other way to do it using JSON_TABLE and JSON_OBJECTAGG to extract distinct JSON array :

    with cte as (
  SELECT GROUP_CONCAT(DISTINCT CONCAT(user_id,',',req_user_id)) as ids
  FROM t 
  WHERE req_user_id= 2 or user_id=2
)
SELECT REGEXP_REPLACE(JSON_OBJECTAGG(item,''), '"|:| |\{|\}', '') as ids
FROM cte, JSON_TABLE(CONCAT('[', ids, ']'), '$[*]'
    COLUMNS(
        item TEXT PATH '$'
    )
) as items;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search