select using sub select

Question

I have a table with stats where each row includes player_id, game_id, and team_id. I am trying to create a query to return result where distinct games are returned where two or more players played together in a game on same team. I trying to create a select statement like this but obviously this is wrong.

SELECT * FROM (
 SELECT * FROM stats AS t1 WHERE player_id IN (array)
 ) AS t2 
WHERE t1.game_id=t2.game_id AND t1.team_id=t2.team_id

Answer 1

If your table has a id you can use this sql with INNER JOIN

SELECT t1.*,t2.*
FROM stats t1
INNER JOIN stats t2 
  ON  t1.game_id=t2.game_id 
  AND t1.team_id=t2.team_id 
  AND t2.id <> t1.id
WHERE t1. player_id IN (array);

The you can modify the SELECT to SELECT DISTINCT t1.game_id

here my result of your table:

MariaDB [Bernd]> SELECT * FROM softball_stats;
+-------------+-----------+---------+---------------+
| sb_stats_id | player_id | game_id | sb_stats_team |
+-------------+-----------+---------+---------------+
|           1 |       100 |       1 |          1000 |
|           2 |       100 |       2 |          1000 |
|           3 |       100 |       3 |          1010 |
|           4 |       101 |       2 |          1000 |
|           5 |       102 |       3 |          1010 |
|           6 |       103 |       1 |          1000 |
+-------------+-----------+---------+---------------+
6 rows in set (0.01 sec)

MariaDB [Bernd]> 
MariaDB [Bernd]> SELECT t1.*,t2.*
    -> FROM softball_stats t1
    -> INNER JOIN softball_stats t2 
    ->   ON  t1.game_id=t2.game_id 
    ->   AND t1.sb_stats_team=t2.sb_stats_team 
    ->   AND t2.sb_stats_id <> t1.sb_stats_id
    -> WHERE t1. player_id IN (100,101);
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
| sb_stats_id | player_id | game_id | sb_stats_team | sb_stats_id | player_id | game_id | sb_stats_team |
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
|           4 |       101 |       2 |          1000 |           2 |       100 |       2 |          1000 |
|           2 |       100 |       2 |          1000 |           4 |       101 |       2 |          1000 |
|           3 |       100 |       3 |          1010 |           5 |       102 |       3 |          1010 |
|           1 |       100 |       1 |          1000 |           6 |       103 |       1 |          1000 |
+-------------+-----------+---------+---------------+-------------+-----------+---------+---------------+
4 rows in set (0.01 sec)

MariaDB [Bernd]>