no result for 27 columns

homer.favenir · #1 (**permalink**) 04-23-09, 03:23

hi,
please can anyone help me
i have query with 27 columns,
but no results was returned
however, if i remove 1 column ( 26 columns ) it return the results,
and if i add a column ( 28 columns ) it also return the results.

any ideas guys?

(

Code:

SELECT
a.idgrading_sheet
,a.SchoolYr
,a.GradingPeriod
,a.`Yr/Section`
,a.`subject`
,a.teacher
,a.StudentName
,a.PerT
,a.Recitation
,a.Quiz
,a.LongQuiz
,a.Project
,a.Assignment
,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'PerT') AS 'PerT(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'PerT') AS 'PerT(E)'
,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Recitation') AS 'Recit(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Recitation') AS 'Recit(E)'
,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Quiz') AS 'Quiz(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Quiz') AS 'Quiz(E)'
,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'LongQuiz') AS 'LongQuiz(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'LongQuiz') AS 'LongQuiz(E)'

,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Project') AS 'Project(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Project') AS 'Project(E)'

,( SELECT distinct R FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Assignment') AS 'Assignment(R)'
,( SELECT distinct E FROM subjpercentage_tbl
    WHERE GradingPeriod = a.GradingPeriod and SubjCode = a.`subject` and Assessment = 'Assignment') AS 'Assignment(E)'

,c.firstnumber
,c.secondnumber


FROM grading_sheettbl a
INNER JOIN subjpercentage_tbl b ON ( b.idsubject_tbl = a.idsubject_tbl )
INNER JOIN gradingformula_tbl c ON ( a.GradingPeriod = c.GradingPeriod )
where a.StudentName = 'Baik Sung Eun'

GROUP BY
a.`subject`
,a.gradingperiod

thanks

gvee · #2 (**permalink**) 04-23-09, 03:31

The query you have posted is incomplete - you are grouping by subject and grading period but there are more non-aggrgated columns in your SELECT list.

Pleas post the actual query (and one of the working versions) please!

r937 · #3 (**permalink**) 04-23-09, 06:50

Quote:

Originally Posted by gvee

you are grouping by subject and grading period but there are more non-aggrgated columns in your SELECT list.

and your point is...?

mysql allows this, it's a "feature"

see Debunking GROUP BY myths

gvee · #4 (**permalink**) 04-23-09, 06:52

D'oh, I keep forgetting that one about MySQL - thanks for pointing that out Rudy

homer.favenir · #5 (**permalink**) 04-23-09, 06:54

it is my actual script, and its working ( if i add another column or deduct a column )
anyway,
i will just remove a column from my script.

thanks

homer.favenir · #6 (**permalink**) 04-23-09, 07:01

but i just keep on thinking why does my script can't output result with just 27 columns..but with 26 or 28 columns it can...
hmmm....

guelphdad · #7 (**permalink**) 04-24-09, 12:20

run your query directly in the mysql client with the 27 columns. do you get output? if so then the problem is in your front end application.

if you have errors in the mysql client when running the script point out the errors here.

homer.favenir · #8 (**permalink**) 04-24-09, 22:47

it has no errors in mysql, but it didnt give any results

healdem · #9 (**permalink**) 04-25-09, 01:00

so the guess would be that the 27th column contains no data so the SQL engine returns nithign because there is nothing to retrun.

you could prove this by, say adding the colmn in another sequence. if it still returns no data then its not the fact its the 27th colmn in the list, but the fact there is no longer any data to return

Internet Services

Web Development

Digital Marketing

Consumer Tech