MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.7.39


SET  sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-- borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `ResearchEntity` (
  `id` int(6) unsigned NOT NULL,
  `name`  varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `ResearchEntity` (`id`, `name`) VALUES
  ('1', 'one'),
  ('2', 'two'),
  ('3', 'three');
  
CREATE TABLE IF NOT EXISTS `ProfileImageEntity` (
  `id` int(6) unsigned NOT NULL,
  `isPrimary` int(1) unsigned NOT NULL,
  `value` varchar(200) NOT NULL,
  `researchId` int(2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `ProfileImageEntity` (`id`,`isPrimary`, `value`,`researchId`) VALUES
  ('1', 0, 'not primary',1),
  ('2', 0, 'not primary',1),
  ('3', 1, 'primary!!!',1),
  
  ('4', 0, 'primary!!!',2),
  ('5', 0, 'not primary',2),
  ('6', 0, 'not primary',2)
  
  ;


CREATE TABLE IF NOT EXISTS `UserNameEntity` (
  `id` int(6) unsigned NOT NULL,
  `isPrimary` int(1) unsigned NOT NULL,
  `value` varchar(200) NOT NULL,
  `researchId` int(2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `UserNameEntity` (`id`,`isPrimary`, `value`,`researchId`) VALUES
  ('1', 0, 'first one, should be returned',1),
  ('2', 0, 'not primary',1),
  ('3', 0, 'primary',1),
  
  ('4', 1, 'primary',3),
  ('5', 0, 'not primary',3),
  ('6', 0, 'not primary',3);

Records: 3  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0


select 
t1.*, 
t2.id AS primaryImageId, 
t2.value AS primaryImageValue, 
t3.id AS primaryUserId,
t3.value AS primaryUserValue
from ResearchEntity t1 

left join ( 
select id, value, researchId from (
  SELECT    *,
              IF(researchId=@last,@_seq:=@_seq+1,@_seq:=1) AS rn,
              @last:=researchId
    FROM      ProfileImageEntity , (SELECT @_seq:=1, @last:=0) r
    ORDER BY  researchId,isPrimary DESC, id ASC ) t 
   where rn=1
   
   ) t2 on t1.id=t2.researchId

left join (
select id, value, researchId from (
  SELECT    *,
              IF(researchId=@last,@_seq:=@_seq+1,@_seq:=1) AS rn,
              @last:=researchId
    FROM      UserNameEntity , (SELECT @_seq:=1, @last:=0) r
    ORDER BY  researchId,isPrimary DESC, id ASC ) t 
   where rn=1
   ) t3 on t1.id=t3.researchId
   
   order by t1.id


id
name
primaryImageId
primaryImageValue
primaryUserId
primaryUserValue


1
one
3
primary!!!
1
first one, should be returned

2
two
4
primary!!!
null
null

3
three
null
null
4
primary
id	name	primaryImageId	primaryImageValue	primaryUserId	primaryUserValue
1	one	3	primary!!!	1	first one, should be returned
2	two	4	primary!!!	null	null
3	three	null	null	4	primary