Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2016
    Posts
    1

    Unanswered: merging multiple rows into one row with multiple colums

    I found this query about merging various rows and columns into on row.

    SELECT `studentdata`.`FirstName`,`studentdata`.`LastName` ,`grades`.`idClass`, `term`.`TermName`,
    JAVA.ExamGrade AS JAVA, JAVA.CourseWork AS JAVACW, HTML.ExamGrade AS HTLM, HTML.CourseWork AS HTMLCW
    From Grades
    LEFT JOIN ereportv3.studentdata ON grades.StudentID = studentdata.StudentID
    LEFT JOIN ereportv3.term ON grades.idTerm = term.IdTerm
    LEFT OUTER JOIN grades JAVA ON (grades.StudentID=JAVA.StudentID AND JAVA.idSubject='JAVA')
    LEFT OUTER JOIN grades HTML ON (grades.StudentID=HTML.StudentID AND HTML.idSubject='HTML')
    WHERE grades.idTerm = '5' and grades.idClass = 'WEB1' AND studentdata.idStatus='Active'
    Group by studentdata.StudentID
    Order by studentdata.LastName

    The problem I am getting is that when I the query is run I am given all the records for java and html for that class in that table instead of just the records of that class for that specified term. I am not sure what I need to do to fix it. I have looked around for a solution but I am yet to come across one that works as I would like. Can someone help or suggest an alternative ??


    HTML Code:
    <table>
      <tr>
        <td>FirstName</td>
        <td>LastName</td>		
        <td>idClass</td>
        <td>Java</td>
    <td>JavaCW</td>
    <td>HTML</td>
    <td>HTMLCW</td>
    <td>TermName</td>
      </tr>
      <tr>
        <td>Eve</td>
        <td>Jackson</td>		
        <td>COMP1</td>
      <td>89</td>
    <td>90</td>
    <td>55</td>
    <td>66</td>
    <td>5</td>
      </tr>
      <tr>
        <td>Eve</td>
        <td>Jackson</td>		
        <td>COMP1</td>
      <td>98</td>
    <td>57</td>
    <td>22</td>
    <td>44</td>
    <td>5</td>
      </tr>
      <tr>
        <td>John</td>
        <td>Doe</td>		
        <td>COMP1</td>
      <td>99</td>
    <td>100</td>
    <td>43</td>
    <td>88</td>
    <td>5</td>
      </tr>
      <tr>
        <td>John</td>
        <td>Doe</td>		
        <td>COMP1</td>
      <td>89</td>
    <td>33</td>
    <td>100</td>
    <td>100</td>
    <td>5</td>
      </tr>
    </table>

    This is the actual result I need
    HTML Code:
    <table>
      <tr>
        <td>FirstName</td>
        <td>LastName</td>		
        <td>idClass</td>
        <td>Java</td>
    <td>JavaCW</td>
    <td>HTML</td>
    <td>HTMLCW</td>
    <td>TermName</td>
      </tr>
      <tr>
        <td>Eve</td>
        <td>Jackson</td>		
        <td>COMP1</td>
      <td>98</td>
    <td>57</td>
    <td>22</td>
    <td>44</td>
    <td>5</td>
      </tr>
      <tr>
        <td>John</td>
        <td>Doe</td>		
        <td>COMP1</td>
      <td>89</td>
    <td>33</td>
    <td>100</td>
    <td>100</td>
    <td>5</td>
      </tr>
    </table>

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You have a query that extracts the data you want in the order you want

    You choose whether to use an existing query to only return the columns you want, or in the presentation layer the data consumer yoy choose the columns you want.

    Its quite common to use the same query for more than one data consumer if there are common columns, rather than write one query for each and every data consumer
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2016
    Posts
    3
    Instead of
    LEFT OUTER JOIN grades JAVA ON (grades.StudentID=JAVA.StudentID AND JAVA.idSubject='JAVA')
    LEFT OUTER JOIN grades HTML ON (grades.StudentID=HTML.StudentID AND HTML.idSubject='HTML')

    Try this
    LEFT OUTER JOIN (SELECT * FROM JAVA WHERE idSubject='JAVA') as JAVA ON (grades.StudentID=JAVA.StudentID)
    LEFT OUTER JOIN (SELECT * FROM HTML WHERE idSubject='HTML') as HTML ON (grades.StudentID=HTML.StudentID)

    Did it work?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Jenoharo,
    Why would you write them that way??? And what does doing this have to do with the poster's question.


    @Kirtney,
    Like Healdem replied shouldn't be a big deal to you whether you get multiple rows or multiple columns. If you want 1 row multiple columns look up pivot tables.
    Dave

Tags for this Thread

Posting Permissions

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