CODEWITHSUNDEEP
mysql
Subash L
Subash L

Reputation: 225

Combine Multiple child rows

Thanks in advance, I just can't seem to get it!

I have two tables

Category 

Id  Name
1   Aptitude
2   English

Mark

UserId  CategoryId   Mark
  1        1          25
  1        2          45
  2        1          34
  3        2          45
  4        1          56
  4        2          66

What I am looking to output is a mysql query is something to this effect

Output

UserId  AptitudeMark   EnglishMark
  1        25            45
  4        56            66

As I am new to mysql I could only get either one category or the other category and I can't get them in the same row as well. Also when the user is having both the category mark, we should consider.

Upvotes: 1

Views: 97

Answers (5)

Chirag Nandaniya
Chirag Nandaniya

Reputation: 495

SELECT main.`id`,A.mark as AptitudeMark,E.mark as EnglishMark FROM `Mark` as main
LEFT JOIN (SELECT id,mark FROM `Mark` WHERE `CategoryId` = 1) as  A ON main.id = A.id
LEFT JOIN (SELECT id,mark FROM `Mark` WHERE `CategoryId` = 2) as  E ON main.id = E.id
GROUP BY main.id

Upvotes: 2

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

If the categories are unknown then you will need to use dynamic sql for this and the query would look like

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when m.CategoryId = ''',
     m.CategoryId,
      ''' then m.Mark end) AS ',
      concat(c.Name,'Mark')
    )
  ) into @sql
from Mark m join Category c on c.Id = m.CategoryId ;

set @sql = concat('select m.UserId, ', @sql, ' from Mark m
                  join Category c on c.Id = m.CategoryId
                  group by m.UserId
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Here is a test case

mysql> select * from Category ;
+------+----------+
| Id   | Name     |
+------+----------+
|    1 | Aptitude |
|    2 | English  |
+------+----------+
2 rows in set (0.00 sec)

mysql> select * from Mark ;
+--------+------------+------+
| UserId | CategoryId | Mark |
+--------+------------+------+
|      1 |          1 |   25 |
|      1 |          2 |   45 |
|      2 |          1 |   34 |
|      3 |          2 |   45 |
|      4 |          1 |   56 |
|      4 |          2 |   66 |
+--------+------------+------+

6 rows in set (0.00 sec)

Running above query will give

+--------+--------------+-------------+
| UserId | AptitudeMark | EnglishMark |
+--------+--------------+-------------+
|      1 |           25 |          45 |
|      2 |           34 |        NULL |
|      3 |         NULL |          45 |
|      4 |           56 |          66 |
+--------+--------------+-------------+

Upvotes: 1

Ullas
Ullas

Reputation: 11556

We can achieve this with the following query with CASE expression and GROUP BY clause, if the number of category(s) is fixed.

Query

select * from
(
    select UserId,
    max(case when CategoryId = 1 then Mark else null end) as AptitudeMark,
    max(case when CategoryId = 2 then Mark else null end) as EnglishMark
    from Mark
    group by UserId
)t
where t.AptitudeMark is not null
and t.EnglishMark is not null;

Upvotes: 1

Arun Krish
Arun Krish

Reputation: 2153

Try this query

SELECT UserId, GROUP_CONCAT(CategoryId,'-',Mark) AS Category_mark
FROM marks
GROUP BY UserId

Which will give you the result like

UserId  Category_mark
  1      1-25,2-45,

Upvotes: 0

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167192

You can use a GROUP_CONCAT, but the results will be somewhat different. Have a look at this:

SELECT `UserId`, GROUP_CONCAT(`CategoryId`), GROUP_CONCAT(`Mark`) FROM `marks`
GROUP BY `UserID`

Upvotes: 1

Related Questions