MySQL – Group_Concat with Joining Multiple Tables

group-concatenationjoin;MySQL

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?

Best Answer

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} )

Related Solutions

MySQL – Using Multiple GROUP_CONCATs with WHERE Clause

SELECT type,
       GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
       GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_without_info      
FROM table1
GROUP BY type ;

Tested at SQL-Fiddle: test-1

If you wanted the results in two rows instead of one, it would be easier, just GROUP BY both type, info:

SELECT 
    type, info,
    GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
      AS list     
FROM table1
GROUP BY type
       , info ;

This can also be used to provide the one-row-per-type format you want:

SELECT
    type,
    MIN( CASE WHEN info = 'yes' THEN list END )
      AS list_with_info,
    MIN( CASE WHEN info = 'no' THEN list END )
      AS list_without_info    
FROM
    ( SELECT 
          type, info,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      GROUP BY type
             , info 
    ) AS grp 
GROUP BY type ;

Tested at SQL-Fiddle: test-2

The above two queries would benefit from an index on (type, info, name)

The following would benefit from an index on (info, type, name):

SELECT
    dt.type,
    grpy.list  AS list_with_info,
    grpn.list  AS list_without_info    
FROM
    ( SELECT DISTINCT type
      FROM table1
    ) AS dt
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'yes'
      GROUP BY type 
    ) AS grpy
      ON grpy.type = dt.type
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'no'
      GROUP BY type 
    ) AS grpn
      ON grpn.type = dt.type ;

Tested at SQL-Fiddle: test-3

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect. To see this, remove the SUMs and GROUP BY and stare at the output. You may see, for example, the same mghours.hourstime showing up multiple times. Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps. Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;

Best Answer

Related Solutions

MySQL – Using Multiple GROUP_CONCATs with WHERE Clause

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

Related Question