php - Mysql subquery return error when more than one record is fetched from db -
i using following code generate merit list of students' performance in different subjects display students according performances.
$qry = "select * ( select (select percentage exams_db subjecttype = 'english') english, (select percentage exams_db subjecttype = 'biology') biology, (select percentage exams_db subjecttype = 'maths') maths, (select percentage exams_db subjecttype = 'science') science, (select percentage exams_db subjecttype = 'socialstudies') socialstudies, (select total_marks exams_db order total_marks desc limit 1) total_marks, (select exam_date exams_db order id desc limit 1) exam_date exams_db group maths union select (select grade exams_db subjecttype = 'english') english, (select grade exams_db subjecttype = 'biology' ) biology, (select grade exams_db subjecttype = 'maths') maths, (select grade exams_db subjecttype = 'science') science, (select grade exams_db subjecttype = 'socialstudies') socialstudies, (select total_marks exams_db order total_marks desc limit 1) total_marks, (select exam_date exams_db order id desc limit 1) exam_date exams_db group maths ) t "; however, error subquery returns more 1 row
however, when edit code below, works 1 user.
$qry = "select * ( select (select percentage exams_db subjecttype = 'english' , user = '$userid') english, (select percentage exams_db subjecttype = 'biology' , user = '$userid') biology, (select percentage exams_db subjecttype = 'maths' , user = '$userid') maths, (select percentage exams_db subjecttype = 'science' , user = '$userid') science, (select percentage exams_db subjecttype = 'socialstudies' , user = '$userid') socialstudies, (select total_marks exams_db user = '$userid' order total_marks desc limit 1) total_marks, (select exam_date exams_db user = '$userid' order id desc limit 1) exam_date exams_db group maths union select (select grade exams_db subjecttype = 'english' , user = '$userid') english, (select grade exams_db subjecttype = 'biology' , user = '$userid') biology, (select grade exams_db subjecttype = 'maths' , user = '$userid') maths, (select grade exams_db subjecttype = 'science' , user = '$userid') science, (select grade exams_db subjecttype = 'socialstudies' , user = '$userid') socialstudies, (select total_marks exams_db user = '$userid' order total_marks desc limit 1) total_marks, (select exam_date exams_db user = '$userid' order id desc limit 1) exam_date exams_db group maths ) t "; but, want display users. so, how can edit first code enable me display users because as second code helping me, allowing me display 1 user.
my database structure follows
user subjecttype percentage grade 109283 english 40% b 345245 biology 80% 832904 science 50% c
i not 100% sure trying output here.
however assuming want list of users , percentages, , users grades, along last exam date , highest total marks user:-
select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db user = '$userid' group user) exam_date left outer join (select user, percentage exams_db subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, percentage exams_db subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, percentage exams_db subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, percentage exams_db subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, percentage exams_db subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db user = '$userid' group user) total_marks on exam_date.user = total_marks.user union select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db user = '$userid' group user) exam_date left outer join (select user, grade exams_db subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, grade exams_db subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, grade exams_db subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, grade exams_db subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, grade exams_db subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db user = '$userid' group user) total_marks on exam_date.user = total_marks.user if isn't want explain not in code. eg, have students table?
edit - list of users (who have done exam) think following might it:-
select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, english.grade, biology.grade, maths.grade, science.grade, socialstudies.grade, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db group user) exam_date left outer join (select user, percentage, grade exams_db subjecttype = 'english' group user) english on exam_date.user = english.user left outer join (select user, percentage, grade exams_db subjecttype = 'biology' group user) biology on exam_date.user = biology.user left outer join (select user, percentage, grade exams_db subjecttype = 'maths' group user) maths on exam_date.user = maths.user left outer join (select user, percentage, grade exams_db subjecttype = 'science' group user) science on exam_date.user = science.user left outer join (select user, percentage, grade exams_db subjecttype = 'socialstudies' group user) socialstudies on exam_date.user = socialstudies.user left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on exam_date.user = total_marks.user note bit dubious total_marks field (your original query getting highest total marks).
possibly more efficient (but untested) be:-
select users.user, exam_dates.examdate, total_marks.totalmarks, max(if(sub1.subjecttype='english', exams_db.percentage, 0)) englishpercentage, max(if(sub1.subjecttype='english', exams_db.grade, 0)) englishgrade, max(if(sub1.subjecttype='biology', exams_db.percentage, 0)) biologypercentage, max(if(sub1.subjecttype='biology', exams_db.grade, 0)) biologygrade, max(if(sub1.subjecttype='maths', exams_db.percentage, 0)) mathspercentage, max(if(sub1.subjecttype='maths', exams_db.grade, 0)) mathsgrade, max(if(sub1.subjecttype='science', exams_db.percentage, 0)) sciencepercentage, max(if(sub1.subjecttype='science', exams_db.grade, 0)) sciencegrade, max(if(sub1.subjecttype='socialstudies', exams_db.percentage, 0)) socialstudiespercentage, max(if(sub1.subjecttype='socialstudies', exams_db.grade, 0)) socialstudiesgrade users left outer join ( select user, subjecttype, max(exam_date) latestsubjectexam exams_db subjecttype in ('english', 'biology', 'maths', 'science', 'socialstudies') group user, subjecttype ) sub1 on users.user = sub1.user left outer join exams_db on sub1.user = exams_db.user , sub1.subjecttype = exams_db.subjecttype , sub1.latestsubjectexam = exams_db.exam_date left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on users.user = total_marks.user left outer join (select user, max(exam_date) examdate exams_db group user) exam_dates on users.user = exam_dates.user group users.user, exam_dates.examdate, total_marks.totalmarks edit again
to try , cope duplicate users / exams in 2nd style query
select exam_date.user, english.percentage, biology.percentage, maths.percentage, science.percentage, socialstudies.percentage, english.grade, biology.grade, maths.grade, science.grade, socialstudies.grade, exam_date.examdate, total_marks.totalmarks (select user, max(exam_date) examdate exams_db group user) exam_date left outer join ( select user, subjecttype, max(exam_date) latestsubjectexam exams_db subjecttype in ('english', 'biology', 'maths', 'science', 'socialstudies') group user, subjecttype ) sub1 on exam_date.user = sub1.user left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'english' group user, exam_date) english on exam_date.user = english.user , sub1.subjecttype = 'english' , sub1.latestsubjectexam = english.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'biology' group user, exam_date) biology on exam_date.user = biology.user , sub1.subjecttype = 'biology' , sub1.latestsubjectexam = biology.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'maths' group user, exam_date) maths on exam_date.user = maths.user , sub1.subjecttype = 'maths' , sub1.latestsubjectexam = maths.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'science' group user, exam_date) science on exam_date.user = science.user , sub1.subjecttype = 'science' , sub1.latestsubjectexam = science.exam_date left outer join (select user, exam_date, percentage, grade exams_db subjecttype = 'socialstudies' group user, exam_date) socialstudies on exam_date.user = socialstudies.user , sub1.subjecttype = 'socialstudies' , sub1.latestsubjectexam = socialstudies.exam_date left outer join (select user, max(total_marks) totalmarks exams_db group user) total_marks on exam_date.user = total_marks.user
Comments
Post a Comment