davidS davidS - 2 months ago 11
MySQL Question

Count in aggregate subquery or join

I have two tables that define game details: a table with the games and a table with moves.

I can tell who's turn it is by

SELECT (COUNT(idx) %2) FROM history WHERE gameID=x


on result=1 it's white and result=0 for black.

Now I want to select a particular series of games, where it is my move.

It is not known if I am white or black in a game.

So now I use another query for the game:

SELECT g.gameID
FROM games AS g
WHERE (g.whitePlayer = 2 or (g.blackPlayer = 2)


Then I use the gameID to see if it's my move. If not, then I skip that game.

I would like to combine these two, I tried to make an aggregate subset but unsuccessfully because of the
COUNT()


For example this does not work and returns nothing:

SELECT *
FROM games as g
WHERE (g.whitePlayer = 2)
AND gameID IN (SELECT gameID
FROM history as h
HAVING (COUNT(h.idx) %2) = 1)


or

SELECT COUNT(h.idx)
FROM history as h
INNER JOIN
(SELECT gameID
FROM games
WHERE (whitePlayer = 2)) As thesegames ON h.gameID = thesegames.gameID
HAVING (COUNT(h.idx) %2) = 1


Does anybody have a clue on how to solve this?

http://sqlfiddle.com/#!2/425fb

Answer

i am not quite finished but this answers my own question

SELECT g.gameID, (count(h.idx)%2) as lastmove
FROM games as g
  inner join history as h on g.gameID=h.gameID 
WHERE (g.whitePlayer=2)
GROUP by g.gameID, g.whitePlayer
HAVING (g.whitePlayer=2 AND lastmove=0)
Comments