Select and add records from a table with different fields

Asked

Viewed 81 times

1

Good afternoon guys, once again I come to ask for help!

I have a table with the games held and the scores, I need to show how many games have been played and the number of goals suffered and performed and etc, the problem and that the table has the leading team and the visiting team, I am not able to print on the screen only the results of together of the same team, in the example of sql this only the attempt to show one time per line, then I will try the remaining results according to the image

To better understand 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 consultation

    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 display the result more or less like this

Tabela

php mysql

  • select case when (placar_mandante_table > placar_visitante_table) then 'v' when (placar_mandante_table = placar_visitante_table) then 'e' Else’d' end) result from table Where mandante_table = '1' Union all select case when (placar_mandante_table > placar_visitante_table) then’d' when (placar_mandante_table = placar_visitante_table) then 'e' Else 'v' end) result from table Where visitnte_table = '1' initial idea

  • You will probably have to make a Union duplicate your table from, the first table being the second and second table.

  • I thought of Union, but I thought it would slow down the process, but I’ll see what will come out here!

  • Motta, I looked at your example and in cool, heheh, I tied a knot in my head, I will pick up and study it to see if I learn more, I never imagined that I could perform the consultation this way!!

  • Add or count? Select SUM(registros) from tabela or Select COUNT(registros) from tabela ?

1 answer

0

Thanks, guys, I got a more viable solution, and I don’t think I’m gonna weigh too much!

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

Thanks for the help!!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.