We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. Java
  3. Introduction
  4. Java If-Else
  5. Discussions

Java If-Else

  • M_F_H
     + 0 comments

    Unfortunately it seems they don't have MySQL 8.0 and MySQL 5.7 does not have WITH, so one has to use subqueries directly, viz

    SELECT ... FROM Contests c
INNER JOIN Colleges   USING (contest_id)
INNER JOIN Challenges USING (college_id)
NATURAL LEFT JOIN ( SELECT ... sum(...) as tv, ...) vv
NATURAL LEFT JOIN ( SELECT ... sum(...) as ts, ...) ss
GROUP BY c.contest_id, c.hacker_id, c.name HAVING sum(ts) > 0 OR sum(tv) > 0

    where NATURAL is equivalent to USING (challenge_id), and it turns out that ORDER isn't needed since the Contests come already in order.

    PS1: in MySQL 8 we could also use NATURAL INNER ... rather than of INNER ... USING(...), but this is not possible in MySQL 5.

    PS2: Note that always total submissions >= accepted ones, and total views >= unique views, so it's enough to test sum(ts)>0 or sum(tv)>0 rather than the sum of all four.