MySQL query to get multiple comma seperated values of a single column from join table

Discussion in 'MySQL' started by slimbachiya, Sep 24, 2011.

0
  1. #1
    Please refere below table. First table is 'Schedule' which has comma seperated team ids (e.g. 1,2). I want to create a single query to fetch team names from joined Team table. Is it possible?

    Schedule
    ------------
    Id date teams
    1 2010-01-15 1,2
    2 2010-01-17 3,4

    Team
    --------
    id team
    1 England
    2 South Africa
    3 Sri Lank
    4 India
     
    slimbachiya, Sep 24, 2011 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    Difficult if you want to do it only in SQL. If you had designed the table right:

    Schedule
    ------------
    Id date team1 team2

    it would be a lot easier.
     
    Rukbat, Sep 27, 2011 IP
  3. slimbachiya

    slimbachiya Peon

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi Rukbat,

    Thanks for your response. Can you please guide me a query to fetch team1 and team2 in a single query as per your design.

    Thanks in advance.
     
    slimbachiya, Oct 4, 2011 IP
  4. Foxtr0t

    Foxtr0t Peon

    Messages:
    39
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    0
    #4
    something like

    select team.name from team, schedule where team.id = schedule.team1 or team.id = schedule.team2

    You'll get two records anyway.
     
    Foxtr0t, Oct 9, 2011 IP