List players, or players in team

darkangel · #1 (**permalink**) 12-21-11, 07:28

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)

r937 · #2 (**permalink**) 12-21-11, 08:36

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

darkangel · #3 (**permalink**) 12-21-11, 08:51

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.

r937 · #4 (**permalink**) 12-21-11, 09:37

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?

darkangel · #5 (**permalink**) 12-21-11, 09:57

Quote:

Originally Posted by r937

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

Good to hear.

Quote:

Originally Posted by r937

what was your question again?

Quote:

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

r937 · #6 (**permalink**) 12-21-11, 10:07

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

dm1 · #7 (**permalink**) 12-21-11, 10:22

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

darkangel · #8 (**permalink**) 12-21-11, 10:40

Quote:

Originally Posted by r937

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

?

darkangel · #9 (**permalink**) 12-21-11, 10:45

Quote:

Originally Posted by dm1

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.

r937 · #10 (**permalink**) 12-21-11, 10:53

Quote:

Originally Posted by darkangel

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

darkangel · #11 (**permalink**) 12-21-11, 14:02

Quote:

Originally Posted by r937

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

dm1 · #12 (**permalink**) 12-21-11, 22:29

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