Get Count from 2 tables

[eevan79]

I want to find users who leave game before game timer end - minimal ratio (lefttime/duration) that a player has to complete a game.

 

Now I have 2 tables: gameplayersand games.

 

In gameplayers table there is filed "left" where is info when user has left the game (example: 1060 - in ms). Also there is filed "reason" where we can see user left reason. For now I can match two words from "reason" filed when user is counted as leaver: "ECONNRESET" and "was dropped" (I am using % for maches).

 

In table games there is filed "duration" where is info of total game duration. So here is my code:

SELECT COUNT(*) 
    FROM `gameplayers`
    WHERE (`leftreason` LIKE('%ECONNRESET%') OR `leftreason` LIKE('%was dropped%') ) 
    AND name= '$username' LIMIT 1

And its working fine. But I want to also count when user leave before game timer ended (duration):

 

WHEN gameplayers.`left`/games.duration < 0.8

 

0.8 is minimal ratio (lefttime/duration) that a player has to complete a game, otherwise I want to count him as leaver. So I need to JOIN table "games" where is gameplayers.left/games.duration < 0.8 and also count this in code above.

 

How to solve this?

[fenway]

You could join on an inequality, but it's better to just place it in the WHERE clause -- have you tried this?

[eevan79]

I have tried:

SELECT gameplayers.id, gameplayers.left, games.duration
    FROM `gameplayers`
    LEFT JOIN games ON duration > 0
    WHERE (`leftreason` LIKE('%ECONNRESET%') OR `leftreason` LIKE('%was dropped%') ) 
    OR gameplayers.left/games.duration < 0.8 
    AND name= '$username' LIMIT 1

But getting false result.

[chintansshah]

How you make join between games and gamesplayers?

I haven't see in ON clause.

[eevan79]

How you make join between games and gamesplayers?

I haven't see in ON clause.

I have already tried, but it's not working.

SELECT gameplayers.id, gameplayers.left, games.duration, games.id, gameplayers.id
    FROM `gameplayers`
    LEFT JOIN games ON games.id = gameplayers.gameid  
    WHERE (`leftreason` LIKE('%ECONNRESET%') OR `leftreason` LIKE('%was dropped%') ) 
    OR gameplayers.left/games.duration < 0.8 
    AND name= '$username' LIMIT 1

 

And there is no changes if i remove this code above or change ratio for:

OR gameplayers.left/games.duration < 0.8 

 

[eevan79]

And thats strange, cause its working when I want to get user play games history (it marks him as he lost the game if this condition is true). Code is:

 

SELECT winner, a.gameid as id, newcolour, datetime, gamename, original, description, kills, deaths, assists, creepkills, creepdenies, neutralkills, name, 
CASE when(gamestate = '17') then 'PRIV' else 'PUB' end as type,
CASE WHEN (kills = 0) THEN 0 WHEN (deaths = 0) then 1000 ELSE (kills*1.0/deaths) end as kdratio,
CASE when ((winner=1 and newcolour < 6) or (winner=2 and newcolour > 5)) AND b.`left`/d.duration >= 0.8  then 'WON' when ((winner=2 and newcolour < 6) or (winner=1 and newcolour > 5)) AND b.`left`/d.duration >= 0.8  then 'LOST' when  winner=0 then 'DRAW' else '$LEAVER' end as outcome 
FROM dotaplayers AS a 
LEFT JOIN gameplayers AS b ON b.gameid = a.gameid and a.colour = b.colour 
LEFT JOIN dotagames AS c ON c.gameid = a.gameid 
LEFT JOIN games AS d ON d.id = a.gameid 
LEFT JOIN heroes as e ON a.hero = heroid 
WHERE LOWER(name) = LOWER('$username') and original <> 'NULL' 
ORDER BY $order $sortdb, d.id $sortdb 
LIMIT $offset, $rowsperpage

 

But code in post above is more simplier. I want to find/count user games where he left before game end (counting ratio of 0.8 left/duration)

[fenway]

You're mixing ORs and ANDs without parens.