MySQL 8.0

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

CREATE TABLE `products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
;

CREATE TABLE `productInstances` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `storeId` INT NULL,
  `productId` INT NOT NULL,
  `price` INT NOT NULL,
  PRIMARY KEY (`id`, `productId`))
;
 
INSERT INTO `products` (`name`) VALUES ('Product 1');
INSERT INTO `products` (`name`) VALUES ('Product 2');

INSERT INTO `productInstances` (`storeId`, `productId`, `price`) VALUES ('1', '1', '111');
INSERT INTO `productInstances` (`productId`, `price`) VALUES ('1', '122');
INSERT INTO `productInstances` (`productId`, `price`) VALUES ('1', '133');
INSERT INTO `productInstances` (`storeId`, `productId`, `price`) VALUES ('1', '1', '115');
INSERT INTO `productInstances` (`storeId`, `productId`, `price`) VALUES ('2', '1', '155');
select p.id productId, 
       pi.id productInstanceId, 
       p.name, 
       pi.storeId, 
       pi.price 
from products p 
left join (
  select *, row_number() over (partition by storeId, productId order by id desc) rn
  from productInstances
) pi on pi.productId = p.id and pi.rn = 1


productId
productInstanceId
name
storeId
price


1
3
Product 1
null
133

1
4
Product 1
1
115

1
5
Product 1
2
155

2
null
Product 2
null
null


select p.id as productId, 
       pi.id as productInstanceId, 
       p.name, 
       pi.storeId, 
       pi.price 
from products p 
left join (
  select pi.* from productInstances pi
  where pi.id = (
    select max(id) 
    from productInstances 
    where productId = pi.productId and storeId <=> pi.storeId 
  )
) pi on pi.productId = p.id


productId
productInstanceId
name
storeId
price


1
5
Product 1
2
155

1
4
Product 1
1
115

1
3
Product 1
null
133

2
null
Product 2
null
null

productId	productInstanceId	name	storeId	price
1	3	Product 1	null	133
1	4	Product 1	1	115
1	5	Product 1	2	155
2	null	Product 2	null	null