mysql repetitive query with many joins

Question

i have the following 4 tables:

Participant      {id_participant, etc.}

Player           {id_participant, name, phone, etc.}

Team             {id_participant, name, phone, etc..}

Couple           {id_participant, id_player1, id_player2}

Games            {id_game, localization, etc.}

GamesParticipant { id_game, id_participant, type (p,t,c) }

On my site I need to make many queries involving participants and the normal way requires many joins per query. The first solution i thought was to create a View with the union of Player, Team, Couple and make queries on that view, but reading the post MySQL view performance i cant use union.

What is the best way to do this and is it possible to do with stored procedures?

thanks

@aprencai. It is good form on stackoverflow to accept an answer (click on the "V") if it solved (most of) your problem. This awards the answerer for fixing your problem and increases your standing within the community. **if there is no good answer to your question, then do not accept it, as this will confuse people looking for answers** — Johan, Apr 28 '11 at 11:40
@aprencai: How come your `Team` table does not have a `id_team` ? Same for your `Player` table. — ypercubeᵀᴹ, Apr 28 '11 at 11:52
@aprencai: What are the relationships between the tables? Have you assigned foreign keys references? — ypercubeᵀᴹ, Apr 28 '11 at 11:57
@ypercube: yes, Player-Team-Couple have foreign keys to Participant (id_participant) — aprencai, Apr 28 '11 at 12:09

score 0 · Accepted Answer · answered Apr 28 '11 at 11:39

   SELECT *
     FROM Participant
LEFT JOIN Player
       ON Player.id_participant = Participant.id_participant
LEFT JOIN Team
       ON Team.id_participant = Participant.id_participant
LEFT JOIN Couple
       ON Couple.id_participant = Participant.id_participant
LEFT JOIN GamesParticipant
       ON GamesParticipant.id_participant = Participant.id_participant
LEFT JOIN Games
       ON Games.id_game= GamesParticipant.id_game

mysql repetitive query with many joins

1 Answers1