locked
MS SQL Subquery with Multiple rows RRS feed

  • Question

  • Hi All,

    Here with I am sharing one SQL query with you. It's working fine when single record is there but giving error when sub-queries returns multiple rows

    select student_feedback_masterId, student_feedback_date,Student_name,student_contact_no,Eductional_background,
    Employmnet_Details,EmailID,City,Country, Other_info,Emp_Name,Emp_Dept ,
    (select Case Question_header_detail_id when NULL then ' ' else 'Yes' end from
    student_feedback_master_Question_header_detailed_Answers
    where Question_header_detail_id IN(28) and
    student_feedback_master_Question_header_Answers IN(select student_feedback_master_Question_header_Answers
    from student_feedback_master_Question_header_Answers
    where student_feedback_masterId IN(select student_feedback_masterId from student_feedback_master
    where student_feedback_date between '2012-04-11' and '2012-04-11') and
    Question_header_master_id IN(5))) as 'Is the Institute DEC Recognized'
    from student_feedback_master where student_feedback_date between '2012-04-11' and '2012-04-11'

    Please give me suggestion ASAP with explanation.

    Thanks in advance.


    Amit

    Wednesday, April 11, 2012 10:27 AM

Answers

  • Since you didn't post any DDL, I can't re-write the query for you.  That said, you have three nested correlated subqueries as a column.  You are also aliasing that column as a string instead of a normal column.  I suggest you re-write the query to not use subqueries since it looks like that is likely possible.  you don't even need that final nested subquery... you already have that table in the query.

    Not knowing anything about the table structure, I'd say you need to go back to the drawing board and try joins... something like this:

    select
    	student_feedback_masterId,
    	student_feedback_date,
    	Student_name,
    	student_contact_no,
    	Eductional_background,
    	Employmnet_Details,
    	EmailID,
    	City,
    	Country,
    	Other_info,
    	Emp_Name,
    	Emp_Dept ,
    	case when answ.question_header_detail_id is null then ' ' else 'Yes' end as "Is the Institute DEC Recognized" -- double quotes
    from
    	student_feedback_master sfm inner join student_feedback_master_Question_header_detailed_Answers dtl
    		on sfm.student_feedback_masterId = dtl.student_feedback_masterId
    	inner join student_feedback_master_Question_header_detailed_Answers answ
    		on answ.some_answer_key = dtl.some_answer_key
    where
    	student_feedback_date between '2012-04-11' and '2012-04-11'
    	and dtl.question_header_detail_id=28
    	and question_header_master_id=5


    • Edited by JustinRush Wednesday, April 11, 2012 11:50 AM
    • Proposed as answer by Naomi N Wednesday, April 11, 2012 7:29 PM
    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:30 PM
    Wednesday, April 11, 2012 11:50 AM

All replies

  • Since you didn't post any DDL, I can't re-write the query for you.  That said, you have three nested correlated subqueries as a column.  You are also aliasing that column as a string instead of a normal column.  I suggest you re-write the query to not use subqueries since it looks like that is likely possible.  you don't even need that final nested subquery... you already have that table in the query.

    Not knowing anything about the table structure, I'd say you need to go back to the drawing board and try joins... something like this:

    select
    	student_feedback_masterId,
    	student_feedback_date,
    	Student_name,
    	student_contact_no,
    	Eductional_background,
    	Employmnet_Details,
    	EmailID,
    	City,
    	Country,
    	Other_info,
    	Emp_Name,
    	Emp_Dept ,
    	case when answ.question_header_detail_id is null then ' ' else 'Yes' end as "Is the Institute DEC Recognized" -- double quotes
    from
    	student_feedback_master sfm inner join student_feedback_master_Question_header_detailed_Answers dtl
    		on sfm.student_feedback_masterId = dtl.student_feedback_masterId
    	inner join student_feedback_master_Question_header_detailed_Answers answ
    		on answ.some_answer_key = dtl.some_answer_key
    where
    	student_feedback_date between '2012-04-11' and '2012-04-11'
    	and dtl.question_header_detail_id=28
    	and question_header_master_id=5


    • Edited by JustinRush Wednesday, April 11, 2012 11:50 AM
    • Proposed as answer by Naomi N Wednesday, April 11, 2012 7:29 PM
    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:30 PM
    Wednesday, April 11, 2012 11:50 AM
  • I second JustinRush, there's probably a better way using joins.

    May we have the exact error message?

    May we have a table definition (please strip it to only the columns you actually need, I don't want to read a table definition with 150 columns when you only use 5 of them in your query)?


    if a problem looks too big, break it into smaller objects

    Wednesday, April 11, 2012 12:31 PM