mysql与LIMIT LEFT JOIN

Question

I have 2 tables:

games:

g_id | country | team_1 | team_2
--------------------------------
  1  | England | Bayern | Chelsea
  2  | England | Bayern | Liverp
  3  | England | Bayern | Ajax

statistic:

s_id | s_time | s_name   | g_id
-----------------------------
 1   |   4    | Alen A.  |  1
 2   |   7    | Dagn S.  |  1
 3   |   11   | Eden D.  |  1
 4   |   22   | Aren A.  |  1
 5   |   8    | Falen B. |  2
 6   |   66   | Poker G. |  2
 7   |   76   | Nuker S. |  2
 8   |   87   | Eben Y.  |  2
 9   |   18   | Falen B. |  3
 10  |   19   | Aalen F. |  3
 11  |   33   | Gased G. |  3
 12  |   44   | Halen B. |  3

And i'm trying to get data from 2 tables with left join where limit

here a query:

SELECT * 
FROM games
LEFT JOIN statistic
ON games.g_id = statistic.g_id
WHERE games.team1 = 'Bayern'
LIMIT 2

result is:

g_id | country | team_1 | team_2 | s_id | s_time | s_name   | g_id
------------------------------------------------------------------
 1   | England | Bayern | Chelsea|  1   |   4    | Alen A.  |  1
 1   | England | Bayern | Chelsea|  2   |   7    | Dags S.  |  1

i need all data from statistics with limit 2 from table "games"! here example what i need:

g_id | country | team_1 | team_2 | s_id | s_time | s_name   | g_id
------------------------------------------------------------------
 1   | England | Bayern | Chelsea|  1   |   4    | Alen A.  |  1
 1   | England | Bayern | Chelsea|  2   |   7    | Dags S.  |  1
 1   | England | Bayern | Chelsea|  3   |   11   | Eden D.  |  1
 1   | England | Bayern | Chelsea|  4   |   22   | Aren A.  |  1
 2   | England | Bayern | Liverp |  5   |   8    | Falen B. |  2
 2   | England | Bayern | Liverp |  6   |   66   | Dags S.  |  2
 2   | England | Bayern | Liverp |  7   |   76   | Alen A.  |  2
 2   | England | Bayern | Liverp |  8   |   87   | Dags S.  |  2

What query i need?

Answer 1

You can try this:

SELECT * 
FROM (SELECT * FROM games WHERE team1 = 'Bayern' ORDER BY g_id LIMIT 2) AS g
LEFT JOIN statistic AS s
ON g.g_id = s.g_id