Good afternoon everyone, once again I come to ask for help!
I have a table with the games played and the boards, I need to show how many games have already been made and the number of goals conceded and made and etc., the problem is that the table has both the home team and the visiting team. to print on the screen only the results of the same team together, in the sql example this is only an attempt to show one team per line, then I will try the remaining results according to the image
To understand better, follow the example.
CREATE TABLE IF NOT EXISTS 'tabela' (
'id_tabela' int(11) unsigned NOT NULL,
'id_competicoes_tabela' int(11) NOT NULL,
'mandante_tabela' int(1) NOT NULL,
'visitante_tabela' int(1) NOT NULL,
'data_tabela' date NOT NULL,
'hora_tabela' time NOT NULL,
'local_tabela' int(11) NOT NULL,
'placar_mandante_tabela' int(11) NOT NULL,
'placar_visitante_tabela' int(11) NOT NULL,
'ativa_tabela' int(1) NOT NULL,
'alterado_tabela' int(1) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela 'tabela'
--
INSERT INTO 'tabela' ('id_tabela', 'id_competicoes_tabela', 'mandante_tabela', 'visitante_tabela', 'data_tabela', 'hora_tabela', 'local_tabela', 'placar_mandante_tabela', 'placar_visitante_tabela', 'ativa_tabela', 'alterado_tabela') VALUES
(10, 26, 10, 11, '2016-07-23', '15:30:00', 8, 6, 3, 0, 0),
(11, 25, 11, 9, '2016-07-22', '14:00:00', 10, 0, 0, 0, 0),
(12, 25, 9, 8, '2016-07-16', '17:30:00', 9, 5, 2, 0, 0),
(13, 25, 11, 10, '2016-07-13', '01:00:00', 9, 4, 1, 0, 1),
(14, 27, 8, 10, '2016-07-15', '14:00:00', 8, 0, 0, 0, 1),
(15, 27, 11, 9, '2016-07-30', '19:00:00', 9, 0, 0, 0, 1),
(16, 27, 9, 8, '2016-07-20', '20:00:00', 10, 5, 2, 0, 1),
(17, 26, 8, 11, '2016-07-23', '00:30:00', 10, 0, 0, 0, 1),
(18, 27, 10, 11, '2016-07-23', '00:30:00', 9, 0, 0, 0, 1),
(19, 25, 11, 8, '2016-07-28', '02:00:00', 10, 0, 0, 0, 0);
And this is the query
SELECT * FROM tabela
WHERE alterado_tabela = 1
AND id_competicoes_tabela = 27
GROUP BY concat(mandante_tabela,visitante_tabela)
ORDER BY data_tabela
I'll have to show the result more or less like this