将PHP依赖的SQL代码转换为完整的SQL

Question

the question will require a bit long of an answer to explain due to my ignorance on SQL. I hope it will not be viewed as vague because I have tried doing it by parts, but then I wont know which part exactly is causing which problem.(It really shows my level of knowledge on SQL.)

I have a code that was originally written in a PHP file, but I have decided I want to create a view table in order for the page to load faster. The reason was because it does a loop to list the ranking of students and was taking too long for the web page to load.

Anyways, here is the code :

    SELECT
      SUM(VCA.meritPoint) AS merit,
      VCA.student_no      AS student_no,
      P.program_code      AS education_level,
      P.name              AS name,
      P.gender            AS gender,
      P.campus_id         AS campus_id
    FROM viewcardactivity VCA
      JOIN pupil P ON P.student_no = VCA.student_no
      JOIN semester S ON S.id = '{$id}' -- MAX() AND (MAX() - 1)
        AND DATE(VCA.tarikh) BETWEEN DATE(s.tarikhStart) AND DATE(s.tarikhEnd)
          WHERE P.campus_id = '{$campus}' -- 1, 2
            AND P.gender= '{$gender}' -- M, F
            AND VCA.level= '{$level}' -- Diploma, Degree
            AND P.program_code = (CONVERT(IF((SUBSTR(REPLACE(`p`.`program_code`,' ',''),3,1) = 1),'Diploma','Degree')USING latin1))
    GROUP BY student_no ORDER BY merit DESC

As the name of the columns suggests, I would like to display more than one instead of specific ids, gender and level provided from the PHP variables.

The example output I would like to have is such as(based on the SQL Fiddle mock data :

table 'viewrankingmerit'   
| merit | student_no | education_level | name | gender | campus_id | 
---------------------------------------------------------------------
|  99   | 111111111  |      Diploma    | Ash  |   M    |     1     | 
---------------------------------------------------------------------
|  87   | 222222222  |      Diploma    |Belle |   F    |     1     | 
---------------------------------------------------------------------
|  85   | 333333333  |      Degree     | Carl |   M    |     1     | 
---------------------------------------------------------------------
|  80   | 444444444  |      Degree     | Deli |   F    |     1     | 
---------------------------------------------------------------------
|  75   | 555555555  |      Diploma    | Eddy |   M    |     2     | 
---------------------------------------------------------------------
|  74   | 666666666  |      Diploma    |Foxxy |   F    |     2     | 
---------------------------------------------------------------------
|  50   | 777777777  |      Degree     | Greg |   M    |     2     | 
---------------------------------------------------------------------
|  20   | 888888888  |      Degree     |Haley |   F    |     2     | 
---------------------------------------------------------------------

As for the semester id, I would like to get the latest 2 ids. Which is the highest and second highest, based on the auto-generated id that will keep on increasing..

I was immediately stuck at trying to get 2 ids from table semester. I've tried using :

JOIN semester S1 ON S1.id = (SELECT MAX(s1.id) FROM semester)
   AND DATE(VCA.tarikh) BETWEEN DATE(s1.tarikhStart) AND DATE(s1.tarikhEnd)

JOIN semester S2 ON S2.id = (SELECT MAX(s2.id)-1 FROM semester)
   AND DATE(VKA.tarikh) BETWEEN DATE(s2.tarikhStart) AND DATE(s2.tarikhEnd)

It was probably a bad reference, but that was the closest solution I got so far.

1) Is it possible to do a table to show all the info?

2) If yes, how to get both S.id, P.campus_id, P.gender and VCA.level. Hoping that the solution would be alike.

3) If no, what is the best solution?

Thanks a lot guys.

[Edit] I've added a demo data in an SQL Fiddle

Answer 1

After some discussion in coments, this is the final result. I think.

select sum(vca.meritPoint)      as merit,
       vca.student_no      AS student_no,
       vca.type            AS education_level,
       p.name              AS name,
       p.gender            AS gender,
       p.campus_id         AS campus_id
 from
   viewcardactiviti vca
      inner join pupil p ON p.student_no = vca.student_no
      inner join (select * from semester order by id desc limit 2) s
              ON (vca.tarikh between s.tarikhStart and s.tarikhEnd
                  AND vca.type = s.level)
group by vca.student_no, vca.type, p.name, p.gender, p.campus_id
order by merit desc, p.campus_id;

See it here on SQLFiddle

If you need to filter for specific configurations like the parameters on your original query just add a WHERE clause.

This subquery (select * from semester order by id desc limit 2) will get the last to semesters based on the ID. And since there is no direct link (foreign key) between semester and viewcardactiviti you can use there join conditions ON (vca.tarikh between s.tarikhStart and s.tarikhEnd AND vca.type = s.level)

If you think that it still need to change anything let me know!