Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: no result for 27 columns

    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    D'oh, I keep forgetting that one about MySQL - thanks for pointing that out Rudy
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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....
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    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.

  8. #8
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    it has no errors in mysql, but it didnt give any results
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •