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?
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