Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Unanswered: List players, or players in team

    Hi,

    With a simplified schema like this:

    Opponent
    - id
    - type (Player or Team)

    Player
    - id

    Team
    - id

    TeamPlayer
    - team_id
    - player_id

    TournamentOpponent
    - tournament_id
    - opponent_id

    ... is it at all possible to get a list of players in the tournament, regardless of whether the opponent is a player or a team. In other words, if they are teams, get the players within those teams. (in a single query)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is possible, but i'm a little concerned about your table design

    here, you have allowed for a player to play on more than one team --

    TeamPlayer
    - team_id
    - player_id

    however, here you have designed it so that a tournament can have 1 or more opponents --

    TournamentOpponent
    - tournament_id
    - opponent_id

    how can a tournament have only one opponent? and what if there are three rows for the same tournament? who plays whom?

    i think you may be missing a table which identifies matches in a tournament

    there is also the very gaping hole that would allow a single player against an entire team, which just doesn't make sense to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Rudy,

    Like I said, it's just a simplification to identify the main issue.

    1. A player can be on more than one team.
    2. I don't understand your second point. A tournament would never have only 1 opponent. That table allows for multiple opponents per tournament, you said it yourself.
    3. The TournamentOpponents table is used for storing stats, etc. for each opponent. There are other tables for matches, etc.
    4. The application would not allow a player to register for a team tournament, and vice versa. Using "inheritance" means that I don't have to duplicate everything. i.e. I don't have to have a "TeamRegistration" as well as a "PlayerRegistration", a "TeamMatch" as well as a "PlayerMatch", etc.

    Glen.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, sounds like i was working on what you posted, and not on what you withheld

    well, i guess you actually do know what you're doing

    what was your question again?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by r937 View Post
    well, i guess you actually do know what you're doing
    Good to hear.

    Quote Originally Posted by r937 View Post
    what was your question again?
    ... is it at all possible to get a list of players in the tournament, regardless of whether the opponent is a player or a team. In other words, if they are teams, get the players within those teams. (in a single query)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, could you start by writing a query that returns all the opponents in a tournament

    i might be able to modify it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2011
    Posts
    13
    Something like this if I understand the requirement correctly:

    Code:
    SELECT p.name
    FROM player p, opponent o, tournamentOpponent t
    WHERE p.id = o.id AND type = 'P' AND o.id = t.opponent_id
    UNION 
    SELECT p.name
    FROM player p, teamPlayer tp, opponent o, tournamentOpponent t
    WHERE p.id = tp.player_id AND tp.team_id = o.id AND type = 'T' AND o.id = t.opponent_id;
    I tested with the following definitions:

    Code:
    CREATE TABLE opponent (id INT, type CHAR);
    CREATE TABLE player (id INT, name VARCHAR(30));
    CREATE TABLE team (id INT, name VARCHAR(30));
    
    CREATE TABLE teamPlayer (team_id INT, player_id INT);
    CREATE TABLE tournamentOpponent (tournament_id INT, opponent_id INT);
    
    INSERT INTO player VALUES (1, 'John');
    INSERT INTO player VALUES (2, 'Tom');
    INSERT INTO player VALUES (3, 'Paul');
    
    INSERT INTO team VALUES (1, 'Nets');
    
    INSERT INTO teamPlayer VALUES (1, 1);
    INSERT INTO teamPlayer VALUES (1, 2);
    
    INSERT INTO opponent VALUES (1, 'T');
    INSERT INTO opponent VALUES (3, 'P');
    
    INSERT INTO tournamentOpponent VALUES (1, 1);
    INSERT INTO tournamentOpponent VALUES (1, 3);

    Dmitry


    --
    http://www.sqlines.com/mysql - MySQL Articles and Free Online SQL Conversion Tool

  8. #8
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by r937 View Post
    okay, could you start by writing a query that returns all the opponents in a tournament

    i might be able to modify it for you
    SELECT *
    FROM TournamentOpponents o
    WHERE o.tournament_id = 123

    ?

  9. #9
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by dm1 View Post
    Something like this if I understand the requirement correctly:

    ...
    Ya, I thought about using a UNION, but wondered if there was an alternative, since my ORM solution might not handle them.

    Anyway, if need be I'll to write a native MySQL query and either use the raw data or map the results to objects myself.

    I need to group the players by country, so I guess I'd put the query in a subselect and select and group against that?

    Thanks for your help.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by darkangel View Post
    SELECT *
    FROM TournamentOpponents o
    WHERE o.tournament_id = 123

    ?
    okay, try this --
    Code:
    SELECT list, your, columns, please
      FROM TournamentOpponents AS t_o
    INNER
      JOIN Opponent AS o
        ON o.id = t_o.opponent_id
    LEFT OUTER
      JOIN Player AS p
        ON p.id = o.id
    LEFT OUTER
      JOIN Team AS t
        ON t.id = o.id
    WHERE t_o.tournament_id = 123
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by r937 View Post
    okay, try this --
    That doesn't appear to work. Using the following data with Dmitry's schema:

    Code:
    INSERT INTO player VALUES (2, 'John');
    INSERT INTO player VALUES (3, 'Tom');
    INSERT INTO player VALUES (4, 'Paul');
    
    INSERT INTO team VALUES (1, 'Nets');
    
    INSERT INTO teamPlayer VALUES (1, 2);
    INSERT INTO teamPlayer VALUES (1, 3);
    
    INSERT INTO opponent VALUES (1, 'T');
    INSERT INTO opponent VALUES (2, 'P');
    INSERT INTO opponent VALUES (3, 'P');
    INSERT INTO opponent VALUES (4, 'P');
    
    INSERT INTO tournamentOpponent VALUES (1, 1);
    INSERT INTO tournamentOpponent VALUES (1, 4);
    I should get all three players (id 2 & 3 from the team, and 4 as a separate player), but I get the following:

    Code:
    tournament, player
    Nets, NULL
    NULL, Paul

  12. #12
    Join Date
    Dec 2011
    Posts
    13
    Hi,

    Without UNION:

    Code:
    SELECT IFNULL(p.name, p2.name)
    FROM tournamentOpponent t, opponent o 
        LEFT OUTER JOIN player p ON (o.id = p.id AND o.type = 'P')
        LEFT OUTER JOIN teamPlayer tp ON (o.id = tp.team_id AND o.type = 'T')
        LEFT OUTER JOIN player p2 ON tp.player_id = p2.id
    WHERE t.opponent_id = o.id
    Dmitry

    --

    http://www.sqlines.com/mysql - MySQL Articles and Free Online SQL Conversion Tool

  13. #13
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Quote Originally Posted by dm1 View Post
    Hi,

    Without UNION:

    ...
    I'm not crazy about the IFNULL() stuff, I think I prefer the UNION option.

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •