MySQL combine row results into a new column

I am trying to set up a query to return data to a VB.Net datagrid. I want to rollup the results into a new column kinda like below:

For example on Match-no:

Date     Sponsor    Match_no     Team
---------------------------------------
1-1-11     Nike        1         Tigers
1-1-11     Nike        1         Bears
2-1-11     Crisco      2         Llamas
2-1-11     Crisco      2         Tigers 

Roll it up into a new column.

Date     Sponsor    Match_no     Teams_playing
---------------------------------------------------
1-1-11    Nike         1          Tigers vs Bears
2-1-11    Crisco       2          Llamas vs Tigers

I tried using "group_concat" but for some reason it rolled up the entire list on teams into a column regardless of match_no.

In this example there would be 4 tables :

开发者_如何学运维

Dates        Sponsors           Match          Team
------       --------         -------        --------
Date_id      Sponsor_id         Match_id       Team_id
Date         Sponsor_name       Match_no       Team_name
                                Sponsor_id     Match_id

---

Try:

  SELECT m.date,
         m.sponsor,
         m.match_no,
         GROUP_CONCAT(m.team, SEPARATOR ' vs ')
    FROM MATCH_NO m
GROUP BY m.date, m.sponsor, m.match_no

---

Your table structure looks suspicious. Dates has no correspondence to Match. Team should be in a separate table with a bridge:

match_team
-----------
match_id
team_id

Or if there are only ever going to be two teams per match, then just

match
---------
match_id
Match_no
sponsor_id
date_id
team_id_a
team_id_b

Normally, one would not create a date table with just id and date, e.g. it doesn't help indexing or searching on the Match table by date.

For your original question, the query would be

select d.date, s.sponsor_name, m.match_no,
    group_concat(teams_playing separator ' vs ') Teams_playing
from match m
inner join dates d on d.date_id = m.date_id
inner join sponsors s on s.sponsor_id = m.sponsor_id
inner join team t on t.match_id = m.match_id
group by m.match_id, d.date, s.sponsor_name, m.match_no

Ref: GROUP_CONCAT