Select and add records from a table with different fields

1

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

    
asked by anonymous 14.07.2016 / 17:53

1 answer

0

Thanks guys, I've got a more viable solution, and I do not think it will weigh heavily at the time of the consultation!

I created a temporary table where it is written every time there is a change or insertion of a new game, so I only consult it, without having to perform subselects, it worked right.

Thanks for the help !!

    
15.07.2016 / 14:04