Mysql 5.7 - get multiple values from sub select

Question

In the query below I am "JOINING" another table where i.isPrimary > 0 and if all i.isPrimary are 0 I just get the first result.

The result set from the query is as expected, but I want to bring more values from each subselect.
I am getting the error: SQL Error (1241): Operand should contain 1 column(s).

How can this query be rewritten in order to get more results from each subselect?

Thanks

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

SQL FIDDLE : http://sqlfiddle.com/#!9/028218/1

SELECT r.*,
 (SELECT i.id FROM ProfileImageEntity i WHERE i.researchId = r.id ORDER BY i.isPrimary DESC, i.id ASC  LIMIT 1 ) AS primaryImageId,
       
 (SELECT i.id FROM UserNameEntity i WHERE i.researchId = r.id ORDER BY i.isPrimary DESC, i.id ASC LIMIT 1 ) AS primaryImageId

FROM ResearchEntity r
ORDER BY id DESC;

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Sep 02 '20 at 07:18
@AkhileshMishra - the tables `ProfileImageEntity` and `UserNameEntity` has a `value` column, I want to add that to the results. `primaryImageId, primaryImageValue primaryUserNameId, primaryUserNameValue` thanks — fatnjazzy, Sep 02 '20 at 07:52
What I understood from your question and comment the you want more columns from sub Query which is not possible. — Akhilesh Mishra, Sep 02 '20 at 08:33
@AkhileshMishra - thats correct. I assume that a join would work better. but the query is tricky... — fatnjazzy, Sep 02 '20 at 08:34

Akhilesh Mishra · Accepted Answer · 2020-09-02T08:50:20.990

What I understood from your question and comment that you want more columns from sub Query which is not possible. So try this query:

It is easy in MySql 8 but you are using MySql 5.7 where it a little bit tricky So try this:

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

left join ( 

  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 
   
   ) t2 on t1.id=t2.researchId and t2.rn=1

left join (
  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 
   ) t3 on t1.id=t3.researchId and t3.rn=1
   
   order by t1.id

DEMO

In MySql 8 using row_number()

with cte as (
SELECT  *,
row_number() over (partition by researchId ORDER BY isPrimary DESC, id ASC) rn
FROM ProfileImageEntity
),

cte1 as (
sELECT  *,
row_number() over (partition by researchId ORDER BY  isPrimary DESC, id ASC) rn
FROM UserNameEntity
)

select 
t1.*, 
t2.id AS primaryImageId, 
t2.value AS primaryImageValue, 
t3.id AS primaryUserId,
t3.value AS primaryUserValue
from ResearchEntity t1 left join cte t2 on t1.id=t2.researchId and t2.rn=1

left join cte1 t3 on t1.id=t3.researchId and t3.rn=1

Bingo. thanks. in mysql8, how is that more simple? thanks again! — fatnjazzy, Sep 02 '20 at 08:35

score 0 · Answer 2 · answered Sep 02 '20 at 07:21

0

try left join

SELECT r.*,i.id FROM ResearchEntity r left join ProfileImageEntity i on r.id = i.researchId 
ORDER BY  i.isPrimary,i.id DESC;

answered Sep 02 '20 at 07:21

Terry Sun

11
5

This cant work with multiple joined tables like my query – fatnjazzy Sep 02 '20 at 07:26

score 0 · Answer 3 · answered Sep 02 '20 at 08:29

0

you just need to left join 2 times

SELECT r.*,i.id,j.id FROM ResearchEntity r left join ProfileImageEntity i on r.id = i.researchId left join UserNameEntity j on r.id=j.researchId  ORDER BY  i.isPrimary,i.id DESC;

answered Sep 02 '20 at 08:29

Terry Sun

11
5

Mysql 5.7 - get multiple values from sub select

3 Answers3