-2

I am trying to get the top 5 opponents I played against.

The tables are as follow

USERS :
 user_id  name               email
       1 : Gait Bakker     : user1@test.nl
       2 : Freek ter Bekke : user2@test.nl
       3 : Jan Zomer       : user3@test.nl
       4 : Mick Dundee     : user4@test.nl
       5 : Sjoerd Trekhaar : user@test.nl

GAMES :
    game_id type training **created_by started_by **opponent_id won_by date
          1    1        1          2              2           4      2 2015-08-10
          2    1        1          5              2           2      5 2015-08-10
          3    1        1          4              4           2      4 2015-08-10
          3    1        1          2              3           3      3 2015-08-10

The problem is the fields with ** and that it needs to count the top 5 opponents.

I can be the game creator (created_by) and I can be the opponent_id (someone else started the game).

Still if I am the opponent I played against another player.

Edit Please use user-id = 2 for example.

As additional : Training needs to be 1 and won_by IS NOT NULL (needs to be filled in)

Result SQL Most played against opponent

user_id = 4,  count = 2
user_id = 3,  count = 1
user_id = 5,  count = 1

So that means I played the most against user-id 4 and then user_id = 3 and user_id = 5

PostMans
  • 338
  • 5
  • 18
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Aug 14 '15 at 09:53
  • @Strawberry : Use user_id = 2, but every logged in user has another user-id. – PostMans Aug 14 '15 at 09:53
  • @jarlh Training = 1 AND won_by IS NOT NULL – PostMans Aug 14 '15 at 09:55
  • @Strawberry, you're Strawberry! – jarlh Aug 14 '15 at 09:56
  • @Strawberry : Pleas use user_id = 2 (for the info : I mean that when I log in I have user_id 2 and I want to know my top 5 opponents, but when Jan Zomer logs in with user_id 3 he wants to know his top 5 opponents) – PostMans Aug 14 '15 at 09:58
  • @Strawberry : I would glad add the SQL, however because of the problem that I (user-id 2) can have started the game or be the opponent I don't even know where to start with the query (I would think a sub query) : We tried this post : http://stackoverflow.com/questions/30984130/add-up-conditional-counts-on-multiple-columns-of-the-same-table – PostMans Aug 14 '15 at 10:03
  • @Strawberry : I edited the post, this is want you mean ? – PostMans Aug 14 '15 at 10:22
  • No, I was looking for CREATE and INSERT statements (just enough to be properly representative of the problem), and a corresponding desired result. – Strawberry Aug 14 '15 at 10:43

1 Answers1

-1

As per my Understanding, Top opponent will be considered based on the Won_By

 SELECT TOP 5 NAME, COUNT(*) as COUNTS FROM USERS
  INNER JOIN
  GAMES
  ON GAMES.WON_BY = USERS.user_id
  WHERE user_id <> Logon_ID
  GROUP BY NAME
  ORDER BY COUNTS

Derive the Logon_ID according to your code

Arun
  • 1
  • Thank you for your reply I mean the top most played matched against an opponent, won or lost (see my edit in my post) – PostMans Aug 14 '15 at 10:22