Better approach to solving this Mysql query

Question

I have two tables similar to the below examples. I wrote a query to combine the two tables and get the total score of the students. The total score consists of (caone+catwo+examscore). I am searching to see if there are other better approaches to solving this in terms of performance and also syntax wise. Thanks

ca table

name  id  course  ca_cat  score
one   1    maths   1       10
one   1    maths   2       6
two   2    maths   1       9
two   2    maths   2       7     

exam table

name    id    course     score
one     1      maths       50
two     2      maths       49  

My query is shown below

WITH
  firstca AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    ca
  WHERE
    cacount =1 ),
  secondca AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    ca
  WHERE
    cacount=2),
  exam AS (
  SELECT
    id,
    name,
    score,
    subject,
  FROM
    exam),
  totalscore AS (
  SELECT
    fca.studentid,
    fca.name,
    fca.subject,
    fca.score AS firstcascore,
    sca.score AS secondcascore,
    ex.score AS examscore,
    (fca.score +sca.score) AS totalca,
    (fca.score+sca.score+ex.score) AS totalscores,
  FROM
    firstca AS fca
  JOIN
    secondca AS sca
  ON
    fca.studentid=sca.studentid
    AND fca.subject=sca.subject
  JOIN
    exam AS ex
  ON
    fca.studentid=ex.studentid
    AND fca.subject=ex.subject

The final result table can be similar to this

name    id   course caone  catwo  exam  totalscore
one      1   maths   10     6      50        66
two      2   maths    9     7      49        65    

Is there a better way to write this query, maybe without the with statement or using subqueries and unions?

I wish to learn from every answer here.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT name, id, course, caone, catwo, exam,
  caone + catwo + exam AS totalscore
FROM (
  SELECT name, id, course, 
    MAX(IF(ca_cat = 1, t2.score, NULL)) AS caone,
    MAX(IF(ca_cat = 2, t2.score, NULL)) AS catwo,
    ANY_VALUE(t1.score) AS exam
  FROM `project.dataset.exam` t1
  JOIN `project.dataset.ca` t2
  USING (name, id, course) 
  GROUP BY name, id, course
)  

If to apply to sample data from your question - output is

Row name    id  course  caone   catwo   exam    totalscore   
1   one     1   maths   10      6       50      66   
2   two     2   maths   9       7       49      65