How to make a select that returns the expected result

Question

How to make a select that returns the expected result

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

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

Ex: And I want to return something like

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

I have tried in 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 related videos, whereas I only need the category with the last video that was recorded in the same .

mysql

asked by anonymous 16.02.2017 / 00:35

2 answers

1

As stated by the Marcelo Bonifazio , the ideal would be to have a column that indicates the upload date of the video. As there is not, I'll assume that last upload has the biggest identifier.

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);

16.02.2017 / 01:39

Language for processing business rules How to check if an array has an attribute?

score 1 · Accepted Answer

If you do not have a field in the video table of type criado_em that only saves the date the video was inserted (which would be the most correct), you can use MAX in id_video (this I have a garbage collection that I want to use, and I want to make sure that the garbage collection is done in the same way. p>

I'm assuming your schema looks 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 on my machine and it worked perfectly:

Ifyoujointhecreated_emfield,youcandothis:

CREATETABLEVideo(id_videointNOTNULLAUTO_INCREMENT,thumbnailvarchar(7),titulovarchar(55),criado_emtimestampNOTNULLDEFAULTCURRENT_TIMESTAMP,/*Dessemodosemprequeumnovocampoforcriado,essecampojávemcomoatualTIMESTAMP*/PRIMARYKEY(id_video));

AndIamemulatingthefollowingtimestampinthevideotable:

INSERTINTO'Video'('id_video','thumbnail','criado_em','titulo')VALUES(1,'Teste1','2017-02-1602:34:28','TesteCategoria1'),(2,'Teste2','2017-02-1502:34:28','TesteCategoria1'),(3,'Teste3','2017-02-1602:34:28','TesteCategoria2'),(4,'Teste4','2017-02-1402:34:28','TesteCategoria3'),(5,'Teste5','2017-02-0502:34:28','TesteCategoria3');

Noticethatvideo4ofthethirdcategorywasaftervideo5(Imanuallychangedittogetthedesiredeffect)byemulatingtheactionofaGROUPBY

Thenewquerycouldberunlikethis:

SELECTMAX(V.CRIADO_EM)ASDATA_CRIACAO,C.ID_CATEGORIA,C.CATEGORIA,V.TITULO,V.THUMBNAILFROMVideoASVINNERJOINCategoriaVideoASVCONV.ID_VIDEO=VC.ID_VIDEOINNERJOINCategoriaASCONC.ID_CATEGORIA=VC.ID_CATEGORIAGROUPBYC.ID_CATEGORIA;

Yourresult: