SO46411645, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists SO46411645_pm_categories_images;
drop table if exists SO46411645_pm_categories;

Create table SO46411645_pm_categories_images (
    image varchar(200),
    fileurl varchar(200),
    catID INT);
    
Create table SO46411645_pm_categories (
    catID int, 
    catname varchar(200),
    `status` tinyint,
    sortorder int,
    parentID int);
    
Insert into SO46411645_pm_categories values (1,'Real Estate', 1,1,null);
Insert into SO46411645_pm_categories values (2,'commercial',1,2,null);
Insert into SO46411645_pm_categories values (3,'Videography',1,3,null);
Insert into SO46411645_pm_categories values (2,'Other',1,4,null);
Insert into SO46411645_pm_categories values (2,'LackingMCVE',1,5,null);

Insert into SO46411645_pm_categories_images values ('guid1.jpg','https://drive.google.com/BusinessID/Postings/',1);
Insert into SO46411645_pm_categories_images values ('guid2.jpg','https://drive.google.com/BusinessID/Postings/',1);
Insert into SO46411645_pm_categories_images values ('guid3.jpg','https://drive.google.com/BusinessID/Postings/',1);
Insert into SO46411645_pm_categories_images values ('guid4.jpg','https://drive.google.com/BusinessID/Postings/',2);
Insert into SO46411645_pm_categories_images values ('guid5.jpg','https://drive.google.com/BusinessID/Postings/',2);
Insert into SO46411645_pm_categories_images values ('guid6.jpg','https://drive.google.com/BusinessID/Postings/',3);
Insert into SO46411645_pm_categories_images values ('guid7.jpg','https://drive.google.com/BusinessID/Postings/',3);
Insert into SO46411645_pm_categories_images values ('guid8.jpg','https://drive.google.com/BusinessID/Postings/',3);
Insert into SO46411645_pm_categories_images values ('guid9.jpg','https://drive.google.com/BusinessID/Postings/',4);
Insert into SO46411645_pm_categories_images values ('guid10.jpg','https://drive.google.com/BusinessID/Postings/',4);
Insert into SO46411645_pm_categories_images values ('guid11.jpg','https://drive.google.com/BusinessID/Postings/',5);
   
    SELECT
CI.Image,
CI.FileURL,
C.catname,
C.`status`,
C.sortorder,
C.parentID,
CI.CatID
FROM SO46411645_pm_categories C
INNER JOIN SO46411645_pm_categories_images  CI
  ON CI.CatID = C.catID 
INNER JOIN (SELECT Min(Image) MI, catID From SO46411645_pm_categories_images group by CATID) Z
 on CI.Image = Z.MI 
 and CI.CatID = Z.CatId
##WHERE C.catname = 'Videography'


absolute service time: 0,39 sec