select based on 2 tables

Question

I have 2 tables with the following structure:

CREATE TABLE `tb_campeonato` (
  `id` int(11) NOT NULL,
  `time` varchar(60) NOT NULL,
  `img` text NOT NULL,
  `jogos` int(11) NOT NULL,
  `vitoria` int(11) NOT NULL,
  `empate` int(11) NOT NULL,
  `derrota` int(11) NOT NULL,
  `Gols Pro` int(11) NOT NULL,
  `Gols Sofridos` int(11) NOT NULL,
  `saldo de gol` int(11) NOT NULL,
  `pontos` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `tb_campeonato` (`id`, `time`, `img`, `jogos`, `vitoria`, `empate`, `derrota`, `Gols Pro`, `Gols Sofridos`, `saldo de gol`, `pontos`) VALUES
(1, 'time1', 'engfoa.png', 0, 0, 0, 0, 0, 0, 0, 0),
(2, 'time2', 'vila.img', 0, 0, 0, 0, 0, 0, 0, 0),
(4, 'time3', 'uss.img', 0, 0, 0, 0, 0, 0, 0, 0),
(3, 'time4', 'medfoa.img', 0, 0, 0, 0, 0, 0, 0, 0),
(5, 'time5', 'edf.img', 0, 0, 0, 0, 0, 0, 0, 0),
(6, 'time6', 'ubm.png', 0, 0, 0, 0, 0, 0, 0, 0),
(7, 'time7', 'ugb.png', 0, 0, 0, 0, 0, 0, 0, 0),
(8, 'time8', 'fer.png', 0, 0, 0, 0, 0, 0, 0, 0),
(9, 'time9', 'aterrado.png', 0, 0, 0, 0, 0, 0, 0, 0),
(10, 'time10', 'odonto.png', 0, 0, 0, 0, 0, 0, 0, 0);

CREATE TABLE `jogos` (
  `id` int(11) NOT NULL,
  `time1` varchar(60) NOT NULL,
  `placar1` int(11) NOT NULL,
  `placar2` int(11) NOT NULL,
  `time2` varchar(60) NOT NULL,
  `rodada` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `jogos` (`id`, `time1`, `placar1`, `placar2`, `time2`, `rodada`) VALUES
(1, 'time1', 0, 0, 'time6', 1),
(2, 'time2', 34, 32, 'time7', 1),
(3, 'time3', 0, 0, 'time8', 1),
(4, 'time4', 0, 0, 'time9', 1),
(5, 'timr5', 0, 0, 'time10', 1);

Ai I need to pull to the table php games in the right order showing all the variables in order (because they are the championship games, then pull showing time1, placar1, placar2, time2) but to leave the most beautiful game table was wanting to pull according to the team, his shield (which lies in the tb_campeonato in the column img. For this I used the Inner Join command that worked almost right (select*from jogos inner join tb_campeonato on tb_campeonato.time=jogos.time1 or jogos.time2=tb_campeonato.time where jogos.rodada=1).

The problem is that the result of this select comes as in the following photo:

However he could not repeat the lines as occurred after the fifth game of the round which would be the last he shows all 5 games again to pull the img of time2, i wanted a way to show all table dice games and and add 2 columns img1(img concerning the time1) and img2(img concerning the time2). any doubt and only talk

Answer 1

do more joins, for example, the first joins to load and display the complete results and a second to grab the team’s image

try something like this

    SELECT bloco1.id_origem AS pontoA, bloco1.id, bloco1.id_destino AS pontoB, bloco1.hora AS horaAB, bloco2.id_destino AS pontoC, bloco2.hora AS horaBC, bloco2.id 

    FROM TabelaTeste bloco1

    JOIN TabelaTeste bloco2
    ON bloco1.id_destino = bloco2.id_origem AND bloco2.id_destino != bloco1.id_origem

    JOIN TabelaTeste bloco3
    ON bloco1.id_origem = bloco3.id_destino AND bloco2.id_origem != bloco3.id_destino AND bloco2.id_destino = bloco3.id_origem

You can see the article here