-1

I have a SQL table that has "PlayerID" and "GameType" columns. Column "GameType" could be "Video Games" or "Virtual Reality". Some of the players played both of these games and I want to see who is playing both, video games and virtual reality.

I tried to complete this with WHERE and GROUPBY statement, but I keep getting the entire list and not just the players who played both. Does anyone know the best solution for this problem? Thank you!!

S3S
  • 24,809
  • 5
  • 26
  • 45
S.Vojnovic
  • 97
  • 2
  • 11
  • 3
    Please copy and paste your SQL query into the question. This is the way SO works. – lit Oct 11 '19 at 20:41
  • 2
    Table structure and sample data would be helpful. If a player has played both, are there two records? – dvo Oct 11 '19 at 20:41
  • Yes, there are two records if player has played both. – S.Vojnovic Oct 11 '19 at 20:42
  • 2
    How do you expect us to help you with the information given? "I tried to complete this with WHERE and GROUPBY, but ..." Where's the query??? Sample input? Expected output? Read this for a guide how to ask question. https://stackoverflow.com/help/how-to-ask – Eric Oct 11 '19 at 22:11

3 Answers3

1

A common approach is using the HAVING clause which is similar to WHERE, but applies after the GROUP BY.

SELECT PlayerId FROM table
GROUP BY PlayerId 
HAVING COUNT(DISTINCT GameType) > 1
S3S
  • 24,809
  • 5
  • 26
  • 45
OwlsSleeping
  • 1,487
  • 2
  • 11
  • 19
  • Before giving answer, probably learn how to use `GROUP BY`. What's the purpose of `GROUP BY` without aggregation? – Eric Oct 11 '19 at 22:13
  • @Eric the aggregation is in the `HAVING` and this answer would give the results the OP expects, based off their question. Also, if you don't have an aggregation in the `SELECT` or `HAVING`, group by will return a *distinct* result set. This is another common use of `GROUP BY` [when you don't have an aggregate function.](https://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct) – S3S Oct 14 '19 at 13:52
1

If there are more options for GameType than just 'Video Games' and 'Virtual Reality', OwlsSleeping's answer might not work. In this case you can use subqueries as a way to identify which players have played a particular type:

select PlayerID
from mytable
where PlayerID in (
    select PlayerID
    from mytable
    where GameType = 'Video Games')

    and PlayerID in (
    select PlayerID
    from mytable
    where GameType = 'Virtual Reality'
)

Alternatively, OwlsSleeping answer can be extended by using a where clause:

SELECT PlayerId FROM table
WHERE GameType in ('Video Games', 'Virtual Reality')
GROUP BY PlayerId 
HAVING COUNT(DISTINCT GameType) > 1
10762409
  • 523
  • 4
  • 19
  • Yes, GameType can be something else, but I need the players who played only video games and virtual reality – S.Vojnovic Oct 11 '19 at 20:59
  • @S.Vojnovic In that case this answer will work. OwlsSleeping answer won't; I've modified it here to fit. I would strongly recommend you add this requirement to the question. – 10762409 Oct 11 '19 at 21:03
  • Thanks for the improvement. Yours seems to be missing FROM, might be worth adding for future readers. – OwlsSleeping Oct 14 '19 at 21:55
-2

What you need to do is create a virtual table by joining your table which has the "PlayerID" and "GameType" columns with the players table. Then you can query the virtual table. Here is an example:

SELECT *
FROM player_game_types
INNER JOIN players
ON player_game_types.PlayerID = players.PlayerID
WHERE player_game_types.GameType = 'Video Games'
INTERSECT
SELECT *
FROM player_game_types
INNER JOIN players
ON player_game_types.PlayerID = players.PlayerID
WHERE player_game_types.GameType = 'Virtual Reality';

Not sure what your table names are, so change player_game_types and players to your actual table names. You can also change the alias if you want.

Oludotun
  • 107
  • 1
  • 3