MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table my_product_photos(
    id int auto_increment primary key,
    product_id int,
    photoname varchar(255),
    flag int,
    ord int
);

insert into my_product_photos(product_id, photoname, flag, ord) 
values (1, "photo11", 0, 1);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (1, "photo12", 0, 2);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (1, "photo13", 0, 3);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (2, "photo22", 0, 2);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (2, "photo21", 0, 1);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (2, "photo23", 1, 3);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (2, "photo24", 0, 4);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (3, "photo31", 0, 0);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (3, "photo32", 0, 0);
insert into my_product_photos(product_id, photoname, flag, ord) 
values (3, "photo33", 0, 0);
create table my_product(
    id int auto_increment primary key,
    name varchar(50)
);

insert into my_product(name) values ('Item1');
insert into my_product(name) values ('Item2');
insert into my_product(name) values ('Item3');
select
    a.*,
    photos.photoname
from my_product a
    join (
        select 
            *,
            rank() over(partition by product_id
                order by flag desc, ord asc, id asc) as r
    from my_product_photos
    ) photos on photos.product_id = a.id and photos.r = 1;
    


id
name
photoname


1
Item1
photo11

2
Item2
photo23

3
Item3
photo31


select 
    *,
    rank() over(partition by product_id
        order by flag desc, ord asc, id desc) as r
from my_product_photos;


id
product_id
photoname
flag
ord
r


1
1
photo11
0
1
1

2
1
photo12
0
2
2

3
1
photo13
0
3
3

6
2
photo23
1
3
1

5
2
photo21
0
1
2

4
2
photo22
0
2
3

7
2
photo24
0
4
4

10
3
photo33
0
0
1

9
3
photo32
0
0
2

8
3
photo31
0
0
3

id	product_id	photoname	flag	ord	r
1	1	photo11	0	1	1
2	1	photo12	0	2	2
3	1	photo13	0	3	3
6	2	photo23	1	3	1
5	2	photo21	0	1	2
4	2	photo22	0	2	3
7	2	photo24	0	4	4
10	3	photo33	0	0	1
9	3	photo32	0	0	2
8	3	photo31	0	0	3