How to make a select that returns the expected result

Question

Hello, I have 3 tables in Mysql Categorias,VideoContemCategoria and Videos I’m trying to make a select where I return all categories and together with each category return the last video that was published in relation to the same.

Ex: And I want to return something like

| Category | title | thumbnail
Cars ; Tesla Motors ; img.png

I’ve tried many ways with Inner join but the fact that there are many videos associated with a category ends up bringing the same category several times along with the related videos, while only need the category with the last video that was registered in the same.

Answer 1

If you do not have a field in the video table of type criado_em that just saves the date that the video was inserted (which would be more correct), you can use MAX in the id_video (if we take into account that the largest id was the last one to be inserted, if your table has a Garbage that recycles id, that logic dies. Therefore the ideal would be a field created in) and give a GROUP BY in the id_categoria.

I’m guessing your schema is like this:

CREATE TABLE Video
  (id_video int NOT NULL AUTO_INCREMENT, thumbnail varchar(7), titulo varchar(55),PRIMARY KEY(id_video));
CREATE TABLE Categoria
  (id_categoria int NOT NULL AUTO_INCREMENT, categoria varchar(55),PRIMARY KEY(id_categoria));
CREATE TABLE CategoriaVideo
  (id_video int, id_categoria int,
  FOREIGN KEY (id_video) REFERENCES Video(id_video),
  FOREIGN KEY (id_categoria) REFERENCES Categoria(id_categoria));
/*-----------------------------------------------------------------------*/  
INSERT INTO Video (thumbnail, titulo) VALUES ("Teste1","TesteCategoria1");
INSERT INTO Video (thumbnail, titulo) VALUES ("Teste2","TesteCategoria1");
INSERT INTO Video (thumbnail, titulo) VALUES ("Teste3","TesteCategoria2");
INSERT INTO Video (thumbnail, titulo) VALUES ("Teste4","TesteCategoria3");
INSERT INTO Video (thumbnail, titulo) VALUES ("Teste5","TesteCategoria3");
/*-----------------------------------------------------------------------*/
INSERT INTO Categoria (categoria) VALUES ("Categoria1");
INSERT INTO Categoria (categoria) VALUES ("Categoria2");
INSERT INTO Categoria (categoria) VALUES ("Categoria3");
/*-----------------------------------------------------------------------*/
INSERT INTO CategoriaVideo (id_video, id_categoria) VALUES (1,1);
INSERT INTO CategoriaVideo (id_video, id_categoria) VALUES (2,1);
INSERT INTO CategoriaVideo (id_video, id_categoria) VALUES (3,2);
INSERT INTO CategoriaVideo (id_video, id_categoria) VALUES (4,3);
INSERT INTO CategoriaVideo (id_video, id_categoria) VALUES (5,3);

Your query would look like this:

SELECT MAX(V.ID_VIDEO) AS ID_VIDEO, C.ID_CATEGORIA, C.CATEGORIA, V.TITULO, V.THUMBNAIL FROM Video AS V 
INNER JOIN CategoriaVideo AS VC ON V.ID_VIDEO = VC.ID_VIDEO 
INNER JOIN Categoria AS C ON C.ID_CATEGORIA = VC.ID_CATEGORIA
GROUP BY C.ID_CATEGORIA;

I tried to create an example in sqlfiddle, but I could not, tested in my machine and worked perfectly:

---

If you join the created field_em, you can do so:

CREATE TABLE Video
  (id_video int NOT NULL AUTO_INCREMENT,
   thumbnail varchar(7),
   titulo varchar(55),
   criado_em timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, /* Desse modo 
   sempre que um novo campo for criado, esse campo já vem com o atual TIMESTAMP */
  PRIMARY KEY(id_video));

And I’m emulating the following timestamp on the video table:

INSERT INTO `Video` (`id_video`, `thumbnail`, `criado_em`, `titulo`) VALUES
(1, 'Teste1', '2017-02-16 02:34:28', 'TesteCategoria1'),
(2, 'Teste2', '2017-02-15 02:34:28', 'TesteCategoria1'),
(3, 'Teste3', '2017-02-16 02:34:28', 'TesteCategoria2'),
(4, 'Teste4', '2017-02-14 02:34:28', 'TesteCategoria3'),
(5, 'Teste5', '2017-02-05 02:34:28', 'TesteCategoria3');

Note that video 4 of the third category was after video 5 (I manually changed to get the desired effect), emulating the action of a Garbage Collector

The new query could be executed like this:

SELECT MAX(V.CRIADO_EM) AS DATA_CRIACAO, C.ID_CATEGORIA, C.CATEGORIA, V.TITULO, V.THUMBNAIL FROM Video AS V 
INNER JOIN CategoriaVideo AS VC ON V.ID_VIDEO = VC.ID_VIDEO 
INNER JOIN Categoria AS C ON C.ID_CATEGORIA = VC.ID_CATEGORIA
GROUP BY C.ID_CATEGORIA;

Its outcome:

Answer 2

As said by Marcelo Bonifazio, ideal would be to have a column indicating the upload date of the video. As there is, I will assume that last upload has the largest handle.

SELECT c.nome,
    v.titulo,
    v.thumbnail
FROM videos v
INNER JOIN VideoContemCategoria vcc ON v.id_video = vcc.id_video
INNER JOIN categorias c ON c.id_categoria = vcc.id_categoria
WHERE v.id_video IN
    (SELECT max(v.id_video)
     FROM videos v
     INNER JOIN VideoContemCategoria vcc ON v.id_video = vcc.id_video
     INNER JOIN categorias c ON c.id_categoria = vcc.id_categoria
     GROUP BY c.id_categoria);