MySQL query join and group according to a certain column

Question

I have two tables as follows:

Teams

team_id   team_name    group_id

10001     team_one     50001
10002     team_two     50001
10003     team_three   50002
10004     team_four    50002
10005     team_five    50003
10006     team_six     50003
10007     team_seven   50004
10008     team_eight   50004

Groups

group_id  group_name

50001     group_one
50002     group_two
50003     group_three
50004     group_four

I want to achieve an sql selection which groups the teams according to the groups follows:

group_one
team_one
team_two

group_two
team_three
team_four

group_three
team_five
team_six

group_four
team_seven
team_eight

So far, I have done the following:

SELECT 
  teams.team_name, 
  teams.soft_name,
  teams.group_id, 
  groups.group_id, 
  groups.group_name 
FROM $table_name 
INNER JOIN groups ON teams.group_id = groups.group_id 
GROUP BY teams.group_id

The problem is, the above query only displays the first four teams from each group. i.e team_one, team_three, team_five, team_seven. How can I change it to achieve my desired results?

Above query is displaying records as it should the output you are looking is possible in mysql but not recommended because of its limitation,do this type of grouping in your application level code that is php ,select * data with join without group by but ordered by group — M Khalid Junaid, Jun 10 '14 at 13:07
@mutiemule You can use [group_concat](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field?rq=1) it will concat multiple rows per group as comma separated list but limitation is it has a default limit of 1024 characters to concat and data will be truncated if it exceeds the limit — M Khalid Junaid, Jun 10 '14 at 13:17

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

The solution is fairly straightforward - I'm unsure why @MKhalidJunaid is saying it's not recommended:

Use a GROUP BY on multiple columns, like so:

SELECT 
  teams.team_name, 
  teams.soft_name,
  teams.group_id, 
  groups.group_id, 
  groups.group_name 
FROM $table_name 
INNER JOIN groups ON teams.group_id = groups.group_id 
GROUP BY teams.group_id, teams.team_id

See This Answer for a complete description.

edited May 23 '17 at 11:57

Community

1
1

answered Jun 10 '14 at 16:07

random_user_name

25,694
7
76
115

perfect, is there a way i can display the group name just once? – mutiemule Jun 11 '14 at 07:13
@mutiemule - I'd need to see your current display PHP in order to advise the best way. There's several possibilities. – random_user_name Jun 11 '14 at 16:08

score 0 · Answer 2 · answered Jun 10 '14 at 13:08

0

I assume you rather would use ORDER BY, not GROUP By so:

  SELECT teams.team_name, teams.soft_name,teams.group_id, groups.group_id, groups.group_name FROM $table_name INNER JOIN groups ON teams.group_id = groups.group_id ORDER BY teams.group_id

answered Jun 10 '14 at 13:08

Sesertin

462
2
11

xQbert · Answer 3 · 2014-06-10T13:25:48.663

In mysql columns names in results must be unique or MYSQL selects one of them and renders it.

SELECT teams.team_name, teams.soft_name,teams.group_id as Team_Group_ID, 
  groups.group_id as Group_group_ID, groups.group_name 
FROM $table_name 
INNER JOIN groups 
  ON teams.group_id = groups.group_id

This is why you're missing data: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

Simply put the extension of mySQL group by behavior must be carefully used. you must either group by all columns that are returned which would result in a unique row or you will have the server 'free to choose any value from each group'

... MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. ...

Removed the group by since no aggregate functions were occurring it's unnecessary. mySQL has .. well Odd behavior when group by's are used without aggregates and if not grouping on non-aggregate columns can produce this odd behavior. It's even documented in their manuals (looking for link) http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause — xQbert, Jun 10 '14 at 13:17

score 0 · Answer 4 · answered Jun 10 '14 at 13:15

If you don't mind a little extra data in your results you can use this:

SELECT gr.group_name, gr.group_id, t.team_name, t.team_id FROM teams t
LEFT JOIN groups gr ON gr.group_id = t.group_id 
ORDER BY gr.group_id, t.team_id

Results look like this:

group_name  group_id team_name      team_id   
group_one   50001    team_one   10001
group_one   50001    team_two   10002
group_two   50002    team_three 10003
group_two   50002    team_four  10004
group_three 50003    team_five  10005
group_three 50003    team_six   10006
group_four  50004    team_seven 10007
group_four  50004    team_eight 10008

I understand it puts basically your whole table out there .. but it has all the groups ant their teams. you can use the "where" to filter out your groups.. Or run to queries, one for groups, then one for each of the sets of teams within.

MySQL query join and group according to a certain column

4 Answers4