Error # 1241 MySQL subquery

Question

Error # 1241 MySQL subquery

Navigation

#1 by (2 votes)

2

How to create two subqueries in MySQL ? effectively and optimized.

The current code throws me the error #1241 - Operando debe tener 1 columna(s) from the phpMyAdmin .

The first subquery calculates the total number of available classes.
The second subquery I want to calculate the average and total votes issued.

My code:

$sql = "SELECT video.title,video.icon,video.duration,video.url,video.author,level.level,
             (
                SELECT COUNT(*) 
                  FROM 'lessons'
                 WHERE video.ident_video = lessons.ident_video
             ),
             (   
                SELECT AVG(vote) AS avg_vote, COUNT(*) votos
                  FROM 'coments'
                 WHERE video.ident_video = coments.ident_video
             )                  
          FROM 'video' 
     LEFT JOIN 'level' on video.ident_level = level.ident_level
         WHERE video.ident_state=? ORDER BY video.ident_video DESC LIMIT 5";

Error: error # 1241 - Operand must have 1 column (s)

Tables SQL :

CREATE TABLE 'level' (
  'ident_level' smallint(3) unsigned not null auto_increment primary key,
  'level' varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'state' (
  'ident_state' smallint(3) unsigned not null auto_increment primary key,
  'state' varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'video' (
  'ident_video' int(11) unsigned not null auto_increment primary key,  
  'title' varchar(255) not null,  
  'icon' varchar(100) not null,    
  'duration' varchar(10) not null,
  'url' varchar(100) not null unique,      
  'author' text not null,
  'ident_level' smallint(3) unsigned not null,
  'ident_state' smallint(3) unsigned not null,   
  foreign key('ident_level') references 'level'('ident_level')
  on delete cascade on update cascade,
  foreign key('ident_state') references 'state'('ident_state')
  on delete cascade on update cascade  
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'lessons' (
  'ident_lesson' smallint(11) unsigned not null auto_increment primary key,
  'page' int(11) unsigned not null,
  'title' varchar(100) not null,
  'url' varchar(100) not null unique,    
  'status' smallint(1) not null,  
  'ident_video' int(11) unsigned not null,   
   foreign key ('ident_video') references 'video'('ident_video')
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE coments(
  'ident_coment' smallint(11) unsigned not null auto_increment primary key,
  'title' varchar(255) not null,
  'coment' text not null,
  'vote' float(3) not null,         
  'ident_video' int(11) unsigned not null,
  'ident_user' int(11) unsigned not null,      
  'status' smallint(1) unsigned not null,
   foreign key ('ident_video') references 'video'('ident_video')
   on delete cascade on update cascade,
   foreign key('ident_user') references 'user'('ident_user')
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

mysql sql mysqli

asked by D.Bulten 16.01.2018 в 23:50

source

1 answer

JQUERY Help with multiplying values of columns and adding the results Copy a Directory and paste it in a new address

score 2 · Accepted Answer

Bulten I have tried to reproduce your data.

You could try using JOIN in the sub-queries, that way you can use the columns you select inside.

It would be more or less like this (I have removed some columns for reasons of simplicity):

SELECT v.title, v.icon, lv.level, ls.total_lessons, c.avg_vote, c.votos
FROM video_20180116 v  
LEFT JOIN
    (
         SELECT ident_video, COUNT(*) total_lessons
            FROM lessons_20180116 GROUP BY ident_video
    )    ls ON (v.ident_video = ls.ident_video)

LEFT JOIN
    (   
        SELECT AVG(vote) avg_vote, ident_video, COUNT(*) votos
        FROM coments_20180116 GROUP BY ident_video
    )   c ON (v.ident_video = c.ident_video)

LEFT JOIN level_20180116 lv on v.ident_level = lv.ident_level
WHERE v.ident_state=? ORDER BY v.ident_video DESC LIMIT 5;

Proof of concept

VIEW DEMO IN REXTESTER

CREATE TABLE 'level_20180116' (
  'ident_level' smallint(3) unsigned not null auto_increment primary key,
  'level' varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'state_20180116' (
  'ident_state' smallint(3) unsigned not null auto_increment primary key,
  'state' varchar(15) not null
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'video_20180116' (
  'ident_video' int(11) unsigned not null auto_increment primary key,  
  'title' varchar(255) not null,
  'icon' varchar(100) not null,        
  'ident_level' smallint(3) unsigned not null,
  'ident_state' smallint(3) unsigned not null,   
  foreign key('ident_level') references 'level_20180116'('ident_level')
  on delete cascade on update cascade,
  foreign key('ident_state') references 'state_20180116'('ident_state')
  on delete cascade on update cascade  
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE 'lessons_20180116' (
  'ident_lesson' smallint(11) unsigned not null auto_increment primary key,
  'title' varchar(100) not null,
  -- 'status' smallint(1) not null,  
  'ident_video' int(11) unsigned not null,   
   foreign key ('ident_video') references 'video_20180116'('ident_video')
   on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

CREATE TABLE coments_20180116(
  'ident_coment' smallint(11) unsigned not null auto_increment primary key,
  'title' varchar(255) not null,
  -- 'coment' text not null,
   'vote' float(3) not null,         
  'ident_video' int(11) unsigned not null,
  -- 'ident_user' int(11) unsigned not null,      
  -- 'status' smallint(1) unsigned not null,
   foreign key ('ident_video') references 'video_20180116'('ident_video')
   on delete cascade on update cascade -- ,
   -- foreign key('ident_user') references 'user'('ident_user')
   -- on delete cascade on update cascade
)engine=InnoDB default charset=utf8 collate=utf8_spanish_ci;

INSERT INTO level_20180116 (level) VALUES ('L1'),('L2');

INSERT INTO state_20180116 (state) VALUES ('st1'),('st2');

INSERT INTO video_20180116 (title,ident_level,ident_state,icon) VALUES 
('v1',1,1,'ic1'),
('v2',1,1,'ic2'),
('v3',2,2,'ic3')
;

INSERT INTO lessons_20180116 (title,ident_video) VALUES 
('les1',1),
('les2',1),
('les3',2)
;

INSERT INTO coments_20180116 (title,ident_video, vote) VALUES 
('com1',1, 8.5),
('com2',1, 9.2),
('com3',2, 7.6)
;

SELECT v.title, v.icon, lv.level, ls.total_lessons, c.avg_vote, c.votos
FROM video_20180116 v  
LEFT JOIN
    (
         SELECT ident_video, COUNT(*) total_lessons
            FROM lessons_20180116 GROUP BY ident_video
    )    ls ON (v.ident_video = ls.ident_video)

LEFT JOIN
    (   
        SELECT AVG(vote) avg_vote, ident_video, COUNT(*) votos
        FROM coments_20180116 GROUP BY ident_video
    )   c ON (v.ident_video = c.ident_video)

LEFT JOIN level_20180116 lv on v.ident_level = lv.ident_level
WHERE v.ident_state=1 ORDER BY v.ident_video DESC LIMIT 5;

Result

title   icon    level   total_lessons   avg_vote            votos
v2      ic2     L1      1               7,59999990463257    1
v1      ic1     L1      2               8,84999990463257    2