0

I have the following scheme

users:        researches:                  researchyear:

uid | name    rid| titleAr                  rid     |  uid     | 
---+--------  ---+------------              --------+----------+
 1 | Peter     1 | Tennis                         1 |        1 |
 2 | Alice     2 | Football                       1 |        2 | 
 3 | Bob       3 | Basketball                     2 |        3 | 
                                                  3 |        1 |
                                                  3 |        3 | 
                                                  3 |        2 |

I would like to get a research with its researchers (users) by specific researcher_Id(uid), i.e when I set user id (1,3 or 2) I would like to get the same result.

id |titleAr    | users_ids | users_names
---+-------+-----------+----------------------------
 3 |Basketball | 1,3,2     | Peter,Bob,Alice

I've tried the following query where I can get every research with its researchers (users):

SELECT r.rId,
       r.titleAr,
       Group_concat(u.userId ) users_ids ,
      Group_concat(u.name )      users_names
FROM   research r
           LEFT JOIN researchyear ry
                     ON r.rId = ry.rId
           LEFT  JOIN users u
                      ON ry.uId = u.userId
GROUP  BY r.rId, r.titleAr

How can I get it for one user?

0

2 Answers 2

1

If you want to list all users names including the uid you want put this in your where clause:

WHERE EXISTS(SELECT * FROM researchyear t WHERE t.rid = r.id AND t.uid = {your_uid} )
0
1

Another option is to apply a row group filter, i.e. use a HAVING clause. If you want to allow only the groups that contain a specific user/researcher, you can do that with the help of conditional count:

SELECT r.rId,
       r.titleAr,
       Group_concat(u.userId ) users_ids ,
       Group_concat(u.name )      users_names
FROM   research r
           LEFT JOIN researchyear ry
                     ON r.rId = ry.rId
           LEFT  JOIN users u
                      ON ry.uId = u.userId
GROUP  BY r.rId, r.titleAr
HAVING COUNT(CASE u.userId /* or: r.uid */ WHEN @specific_user_id THEN 1 END) > 0 ;

Note that conditional count can have various implementations in MySQL. One of the shortest (if not the shortest one) is using SUM, so in your case it would be

...
HAVING SUM(u.userId = @specific_user_id) > 0 ;
/* or: SUM(r.uid = @specific_user_id) > 0 */

Unlike the EXISTS method suggested previously, this avoids hitting underlying tables more than once, which might result in better performance, although on small datasets the improvement, if any, would likely be negligible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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