CODEWITHSUNDEEP
phpmysql
mutiemule
mutiemule

Reputation: 2549

MySQL query join and group according to a certain column

I have two tables as follows:

  1. 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

  2. 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?

Upvotes: 1

Views: 164

Answers (4)

random_user_name
random_user_name

Reputation: 26180

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.

Upvotes: 1

xQbert
xQbert

Reputation: 35343

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. ...

Upvotes: 0

Silvertiger
Silvertiger

Reputation: 1680

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.

Upvotes: 0

Sesertin
Sesertin

Reputation: 462

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

Upvotes: 0

Related Questions