CODEWITHSUNDEEP
mysqlsql
naletups
naletups

Reputation: 15

SQL multiple JOINs or subqueries but avoid cartesian product

I want to realize an SQL database for a game. There are a number of players that participate in different tournaments. For each tournament, a player has a separate account. All games are listed in one large table in which the tournament accounts are used to describe winner, loser, along with the score of the game.

The schema is given in http://sqlfiddle.com/#!9/55378a or here again


    CREATE TABLE `players` (
      `id` int NOT NULL,
      `name` varchar(5),
      PRIMARY KEY (`id`)
    );
    CREATE TABLE `tournamentAccounts` (
      `tId` int NOT NULL,
      `playerId` int NOT NULL,
      `handicap` int NOT NULL DEFAULT 10,
      PRIMARY KEY (`tId`)
    );
    CREATE TABLE `games` (
      `gameId` int NOT NULL,
      `winnerTId` int NOT NULL,
      `loserTId` int NOT NULL,
      `score` int NOT NULL DEFAULT 0,
      PRIMARY KEY (`gameId`)
    );
    INSERT INTO `players` (`id`, `name`) VALUES
    (1, 'a'), (2, 'b'), (3, 'c');
    INSERT INTO `tournamentAccounts` (`tId`, `playerId`, `handicap`) VALUES
    (1, 1, 10), (2, 1, 2), (3, 2, 0);
    INSERT INTO `games` (`gameId`, `winnerTId`, `loserTId`, `score`) VALUES
    (1, 1, 3, 3), (2, 1, 3, 2), (3, 3, 1, 6);

What I want to achieve: List for a specific player all tournament scores, i.e. handicap + scorepoints of won games - scorepoints of lost games. For the given inputs, the result set should contain two rows with total scores 9 (for tId=1) and 2 (for tId=2), respectively. The example here is simplified, as in my example there are more conditions to match between the tournamentAccounts and games tables (e.g. time slots etc.), but I guess I can extend it myself once I understood the basic approach :-)

My approaches until now failed as I cannot get a nice JOIN or subqueries to work (I would like to avoid stored procedures).

Attempt 1: straight forward join


    SELECT t.*, (t.handicap +COALESCE(SUM(w.score),0) -COALESCE(SUM(l.score),0)) AS score
    FROM tournamentAccounts t
    LEFT JOIN games w ON w.winnerTId = t.tId
    LEFT JOIN games l ON l.loserTId = t.tId
    WHERE playerId = 1
    GROUP BY t.tId

Although this returns the correct number of rows, the double LEFT JOIN causes a cartesian product as it seems: the two won games are joined with the lost game into two datasets, hence 10 + 3 - 6 + 2 - 6. This effect obviously becomes worse the more matching rows I have in the games table.

Attempt 2: UNION with JOIN (similar to sql avoid cartesian product)


    SELECT SUM(COALESCE(x.aa,0))
    FROM
    ((SELECT -l.score AS aa FROM games l LEFT JOIN tournamentAccounts t ON l.loserTId = t.tId WHERE t.playerId = 1)
    UNION
    (SELECT w.score AS aa FROM games w LEFT JOIN tournamentAccounts t ON w.winnerTId = t.tId WHERE t.playerId = 1)) x

With this I get the proper score value summed up, however it is not yet combined with the corresponding handicap value, and also I don't know how to extend from here to cover all tournament accounts of that player (here, I just took a small snapshot of data) in an SQL manner.

Upvotes: 1

Views: 824

Answers (2)

Preeti
Preeti

Reputation: 222

How about following:-

SELECT t.*, (t.handicap + coalesce(wscore,0) - coalesce(lscore,0)) AS score
    FROM tournamentAccounts t 
    LEFT JOIN (
         select sum(score) wscore, winnerTId wid 
          from games 
          group by winnerTid
      ) as w ON w.wid = t.tid
    left join (
        select sum(score) lscore, loserTid lid 
         from games 
          group by loserTid
    ) as l ON l.lid = t.tid
    where playerId = 1

I got the result as

tId playerId    handicap    score
1   1           10          9 
2   1           2           2

Upvotes: 1

ysth
ysth

Reputation: 98398

I would just make the games portion of your query into a union, not the whole thing:

SELECT t.*, (t.handicap +COALESCE(SUM(win_score),0) -COALESCE(SUM(loss_score),0)) AS score
FROM tournamentAccounts t
LEFT JOIN (
    SELECT w.winnerTId AS tId, w.score AS win_score, 0 AS loss_score FROM games w
    UNION ALL
    SELECT l.loserTId, 0, l.score FROM games l
) games_won_or_lost ON games_won_or_lost.tId=t.tId
WHERE playerId = 1
GROUP BY t.tId

The other alternative is to undo the effects of the cartesian product. You know the win score is too high by a factor of the number of lost games, so replace SUM(w.score) with ROUND(SUM(w.score)/GREATEST(COUNT(DISTINCT l.gameId),1)). And similarly, SUM(l.score) becomes ROUND(SUM(l.score)/GREATEST(COUNT(DISTINCT w.gameId),1)).

fiddle

Upvotes: 1

Related Questions