2

I want to write a select statement that with some fixed columns and dynamic columns. here Dynamic Columns names are the rows in another table.

I have tried this but getting error. Please tell me where my error is

set @sql=(Select subject from tbl_subjects where C_Id=22);
SELECT 
    ta.rollno,
    ta.StdNm,
    tc.C_Name,
    @sql
FROM
    tbl_cmarks tm,
    tbl_admission ta,
    tbl_classes tc
WHERE
    tm.Cid = tc.C_Id
        AND ta.rollno = tm.rollno
        AND tm.Cid = 22 and tm.Examid=9 

Mysql showing:

Error Code: 1242. Subquery returns more than 1 row

Could any one tell me how to do this in mysql?

  • When you run the query Select subject from tbl_subjects where C_Id=22 how many rows are returned? Are they identical values? Can you edit your post with some sample data and the desire result? – Taryn Jul 16 '13 at 13:39
  • Thank you @bluefeet for early response. Select subject from tbl_subjects where C_Id=22 will return 7 subject names – Narendar_CH Jul 16 '13 at 13:45
  • that 7 subject names are column names in tbl_cmarks. I want to select records from tbl_camrks, but don't know how many columns to pass in select statement, thats why i am selecting the rows from subjects table identical to class 22 – Narendar_CH Jul 16 '13 at 13:47
  • If you have the column name in tbl_cmarks as different subjects, it sounds like you have a design flaw. Can you edit you original question with the table structures, sample data and then the desired result? Or even better create a sql fiddle with your table details. – Taryn Jul 16 '13 at 13:53
  • Plz look at this sqlfiddle sqlfiddle.com/#!2/59ec8/3 – Narendar_CH Jul 16 '13 at 14:27
12

Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:

create table tbl_subjects
(
  sub_id int,
  subject_name varchar(25)
);

create table tbl_cmarks
(
  c_id int,
  examid int,
  rollno int,
  sub_id int,
  mark int
);

Using something similar to above will allow you to add new subjects without having to alter your table. Then you will just join the tables on the sub_id to get the list of subjects for each class.

select ta.rollno,ta.StdNm,tc.C_Name,
  tm.mark,
  s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9;

See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:

select ta.rollno,
  ta.StdNm,
  tc.C_Name,
  max(case when s.subject = 'English' then tm.mark end) Emglish,
  max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9
group by  ta.rollno, ta.StdNm, tc.C_Name;

See Demo.

But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.

First, you will create the list of the classes:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

See Demo. This will give you the list of classes for each c_id. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

SET @query 
  = CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, ' 
            FROM tbl_cmarks tm
            INNER JOIN tbl_classes tc
              ON tm.Cid = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.rollno = tm.rollno
            where tm.Cid = 22 
              and tm.Examid=9');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

  • All this and no information_schema involvement? Wow !!! This is worth a +2, but you'll have to settle for +1 !!! – RolandoMySQLDBA Jul 16 '13 at 14:44
  • 1
    @RolandoMySQLDBA Thanks! I also edited my answer to suggest a table redesign, IMO the tbl_cmarks is poorly design and the OP will have a much easier time if they redesign the table. – Taryn Jul 16 '13 at 14:46
  • Thank you @bluefeet. If i create the table structure like as you mentioned above i have a problem i.e., suppose student in Class 22 means XA having 7 subjects, here i need to add 7 records only identical field is sub_id, if class XA have 60 students 60*7 records for 1 single Exam_Id, if Exams are 6 for XA 60*7 records for also 6 times need to capture in table.So the table will have more data with repeated row values. thats y i have taken the structure like i was mentioned, here Subject Names will be max 9 identical for all classes from 1-10th. – Narendar_CH Jul 16 '13 at 15:16
  • BTW, for querying a large table, might want ` LIMIT nnn` at end of @query. I was doing something like this via phpmyadmin to an AWS DB, and it seemed to be hanging. I finally realized w/o LIMIT I was asking it to send ALL the rows to phpmyadmin to be formatted for display. – ToolmakerSteve Feb 22 at 2:26
0

Your first command set @sql=(Select subject from tbl_subjects where C_Id=22); gives you an error because subquery returns more than 1 row. You either need to use cursors, and iterate through resultset or do something like

set @sql=(Select GROUP_CONCAT(DISTINCT subject SEPARATOR  ',' ) 
 from tbl_subjects where C_Id=22);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.