0qVXLh3e

1SELECT 
2
3    team1.name AS team1,
4    teamOne.name AS city1,
5    team2.name AS team2,
6    teamTwo.name AS city2,
7    game.date AS data, 
8    game.status,
9    placeGame.name AS place
10
11    FROM game
12
13    LEFT JOIN team AS team1 ON game.team1_id = team1.id 
14    LEFT JOIN team AS team2 ON game.team2_id = team2.id
15    LEFT JOIN city AS teamOne ON team1.city_id = teamOne.id 
16    LEFT JOIN city AS teamTwo ON team2.city_id = teamTwo.id 
17    LEFT JOIN city AS placeGame ON game.game_city = placeGame.id
18	
19SELECT team.name, GROUP_CONCAT(player.name SEPARATOR ', ')
20
21    FROM team
22
23    LEFT JOIN player ON player.team_id = team.id 
24    GROUP BY team.id
25	
26Unknown column 'team.id' in 'on clause'
27	
28SELECT 
29
30    team1.name AS team1,
31    teamOne.name AS city1,
32    team2.name AS team2,
33    teamTwo.name AS city2,
34    game.date AS data, 
35    game.status,
36    placeGame.name AS place,
37    GROUP_CONCAT(player.name SEPARATOR ', ')
38
39    FROM game
40
41    LEFT JOIN team AS team1 ON game.team1_id = team1.id 
42    LEFT JOIN team AS team2 ON game.team2_id = team2.id
43    LEFT JOIN city AS teamOne ON team1.city_id = teamOne.id 
44    LEFT JOIN city AS teamTwo ON team2.city_id = teamTwo.id 
45    LEFT JOIN city AS placeGame ON game.game_city = placeGame.id
46
47    LEFT JOIN player ON player.team_id = team.id 
48    GROUP BY team.id
49	
50-- phpMyAdmin SQL Dump
51-- version 4.3.11
52-- http://www.phpmyadmin.net
53--
54-- Хост: 127.0.0.1
55-- Время создания: Дек 31 2017 г., 17:06
56-- Версия сервера: 5.6.24
57-- Версия PHP: 5.6.8
58
59SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
60SET time_zone = "+00:00";
61
62
63/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
64/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
65/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
66/*!40101 SET NAMES utf8 */;
67
68--
69-- База данных: `lesson15`
70--
71
72-- --------------------------------------------------------
73
74--
75-- Структура таблицы `city`
76--
77
78CREATE TABLE IF NOT EXISTS `city` (
79  `id` int(11) NOT NULL,
80  `name` varchar(256) NOT NULL
81) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
82
83--
84-- Дамп данных таблицы `city`
85--
86
87INSERT INTO `city` (`id`, `name`) VALUES
88(1, 'Санкт-Петербург '),
89(2, 'Москва'),
90(3, 'Барселона'),
91(4, 'Фулем Роуд'),
92(5, 'Траффорд');
93
94-- --------------------------------------------------------
95
96--
97-- Структура таблицы `game`
98--
99
100CREATE TABLE IF NOT EXISTS `game` (
101  `id` int(11) NOT NULL,
102  `team1_id` int(11) NOT NULL,
103  `team2_id` int(11) NOT NULL,
104  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
105  `game_city` int(11) NOT NULL,
106  `status` int(11) NOT NULL
107) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
108
109--
110-- Дамп данных таблицы `game`
111--
112
113INSERT INTO `game` (`id`, `team1_id`, `team2_id`, `date`, `game_city`, `status`) VALUES
114(1, 2, 5, '2017-12-30 20:05:49', 1, 0),
115(2, 1, 3, '2017-12-30 20:05:49', 3, 1),
116(3, 3, 6, '2017-11-30 20:06:30', 5, 1),
117(4, 4, 5, '2017-12-30 20:06:30', 4, 0);
118
119-- --------------------------------------------------------
120
121--
122-- Структура таблицы `player`
123--
124
125CREATE TABLE IF NOT EXISTS `player` (
126  `id` int(11) NOT NULL,
127  `name` varchar(256) NOT NULL,
128  `team_id` int(11) NOT NULL
129) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
130
131--
132-- Дамп данных таблицы `player`
133--
134
135INSERT INTO `player` (`id`, `name`, `team_id`) VALUES
136(1, 'Андрей Лунев', 1),
137(2, 'Юрий Лодыгин', 1),
138(3, 'Михаил Кержаков', 1),
139(4, 'Иван Новосельцев', 1),
140(5, 'РЕБРОВ Артём ', 2),
141(6, 'ПЕТКОВИЧ Марко ', 2),
142(7, 'КОМБАРОВ Дмитрий ', 2),
143(8, 'ГЛУШАКОВ Денис ', 2),
144(9, 'Ракитич Иван', 3),
145(10, 'Арда Туран', 3),
146(11, 'Серджи Бускетс', 3),
147(12, 'Давид Костас', 3),
148(13, 'Давид Луиз', 4),
149(14, 'Виллиан', 4),
150(15, 'Педро', 4),
151(16, 'Альваро Мората', 4),
152(17, 'Амир Натхо', 5),
153(18, 'Иван Галанин', 5),
154(19, 'Михаил Лысов', 5),
155(20, 'Иван Лапшов', 5),
156(21, 'Антонио Валенсия', 6),
157(22, 'Поль Погба', 6),
158(23, 'Джесси Лингард', 6),
159(24, 'Ромелу Лукаку', 6);
160
161-- --------------------------------------------------------
162
163--
164-- Структура таблицы `team`
165--
166
167CREATE TABLE IF NOT EXISTS `team` (
168  `id` int(11) NOT NULL,
169  `name` varchar(256) NOT NULL,
170  `city_id` int(11) NOT NULL
171) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
172
173--
174-- Дамп данных таблицы `team`
175--
176
177INSERT INTO `team` (`id`, `name`, `city_id`) VALUES
178(1, 'Зенит', 1),
179(2, 'Спартак', 2),
180(3, 'Барселона', 3),
181(4, 'Челси', 4),
182(5, 'Локомотив', 2),
183(6, 'Манчестер Юнайтед ', 5);
184
185--
186-- Индексы сохранённых таблиц
187--
188
189--
190-- Индексы таблицы `city`
191--
192ALTER TABLE `city`
193  ADD PRIMARY KEY (`id`);
194
195--
196-- Индексы таблицы `game`
197--
198ALTER TABLE `game`
199  ADD PRIMARY KEY (`id`);
200
201--
202-- Индексы таблицы `player`
203--
204ALTER TABLE `player`
205  ADD PRIMARY KEY (`id`);
206
207--
208-- Индексы таблицы `team`
209--
210ALTER TABLE `team`
211  ADD PRIMARY KEY (`id`);
212
213--
214-- AUTO_INCREMENT для сохранённых таблиц
215--
216
217--
218-- AUTO_INCREMENT для таблицы `city`
219--
220ALTER TABLE `city`
221  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
222--
223-- AUTO_INCREMENT для таблицы `game`
224--
225ALTER TABLE `game`
226  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
227--
228-- AUTO_INCREMENT для таблицы `player`
229--
230ALTER TABLE `player`
231  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=25;
232--
233-- AUTO_INCREMENT для таблицы `team`
234--
235ALTER TABLE `team`
236  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
237/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
238/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
239/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
240	
241SELECT 
242
243    team1.name AS team1,
244    teamOne.name AS city1,
245    GROUP_CONCAT(player.name SEPARATOR ', ') AS comanda1,
246
247    team2.name AS team2,
248    teamTwo.name AS city2,
249    GROUP_CONCAT(player.name SEPARATOR ', ') AS comanda2,
250
251    game.date AS data, 
252    game.status,
253    placeGame.name AS place
254
255    FROM game
256
257    LEFT JOIN team AS team1 ON game.team1_id = team1.id 
258    LEFT JOIN team AS team2 ON game.team2_id = team2.id
259    LEFT JOIN city AS teamOne ON team1.city_id = teamOne.id 
260    LEFT JOIN city AS teamTwo ON team2.city_id = teamTwo.id 
261    LEFT JOIN city AS placeGame ON game.game_city = placeGame.id
262    LEFT JOIN player ON player.team_id = team1.id
263
264    GROUP BY team1.name