Adjust query sql according to available discipline

Question

I have this query, working well, but it only works if I have data for the two disciplines PORTUGUES and MATEMATICA, how can I do so that, without harming the result, regardless of there being only one or two disciplines I can get the results, follows below only the part of the query that I think necessary, if I need to put the rest.

-- Campos a selecionar.
SELECT
AVG(IFNULL(pqas.fraction, 0)) * 10 AS MediaPT,
AVG(IFNULL(mqas.fraction, 0)) * 10 AS MediaMT,
gg.finalgrade AS NotaGeral,
u.firstname AS Aluno,
u.username AS Usuario,
u.lastname AS siem,
--  u.id, -- removido
u.department AS Turma,
u.institution AS Escola,
pqc.name AS catp,
mqc.name AS catm

FROM mdl_user u

INNER JOIN mdl_grade_grades gg ON gg.userid = u.id
INNER JOIN mdl_grade_items gi ON gi.`id` = gg.`itemid`
INNER JOIN mdl_question_attempt_steps pqas ON u.id = pqas.userid
INNER JOIN mdl_question_attempts pqa ON pqa.id = pqas.questionattemptid
INNER JOIN mdl_question pq ON pq.id = pqa.questionid
INNER JOIN mdl_quiz_slots pqs ON pqs.questionid = pqa.questionid AND pqs.slot = pqa.slot
INNER JOIN mdl_quiz pquiz ON pquiz.id = pqs.quizid
INNER JOIN mdl_question_categories pqc ON pqc.id = pq.category
INNER JOIN mdl_question_attempt_steps mqas ON u.id = mqas.userid
INNER JOIN mdl_question_attempts mqa ON mqa.id = mqas.questionattemptid
INNER JOIN mdl_question mq ON mq.id = mqa.questionid
INNER JOIN mdl_quiz_slots mqs ON mqs.questionid = mqa.questionid AND mqs.slot = mqa.slot
INNER JOIN mdl_quiz mquiz ON mquiz.id = mqs.quizid
INNER JOIN mdl_question_categories mqc ON mqc.id = mq.category    

-- Parte do WHERE para filtrar apenas PORTUGUES nas tabelas p.
WHERE SUBSTR(pqc.name, 8) = "PORTUGUES"

-- Parte do WHERE para filtrar apenas matemática nas tabelas m.
AND SUBSTR(mqc.name, 8) = "MATEMATICA"

-- Final da query.
GROUP BY turma, u.id
ORDER BY turma ASC, `Aluno` ASC

Answer 1

How to do

You can use the clause OR:

SELECT
    AVG(IFNULL(pqas.fraction, 0)) * 10 AS MediaPT,
    AVG(IFNULL(mqas.fraction, 0)) * 10 AS MediaMT,
    gg.finalgrade AS NotaGeral,
    u.firstname AS Aluno,
    u.username AS Usuario,
    u.lastname AS siem,
--  u.id, -- removido
    u.department AS Turma,
    u.institution AS Escola,
    pqc.name AS catp,
    mqc.name AS catm
FROM mdl_user u

WHERE SUBSTR(pqc.name, 8) = "PORTUGUES"
   OR SUBSTR(mqc.name, 8) = "MATEMATICA"

GROUP BY turma, u.id
ORDER BY turma ASC, `Aluno` ASC

---

Explaining

Row:

WHERE SUBSTR(pqc.name, 8) = "PORTUGUES"
       OR SUBSTR(mqc.name, 8) = "MATEMATICA"

The function OR will cause the query to bring the results of:

SUBSTR(pqc.name, 8) = "PORTUGUES" OR (OR) SUBSTR(mqc.name, 8) = "MATEMATICA"

Answer 2

amended response after comments

Using the via validation OR (since the control fields are of different tables), add also a validation in the field query; if it exists (Portuguese or math), calculate the average.
This validation is required to display the two averages (if any) or only one:

SELECT
    CASE WHEN (SUBSTR(pqc.name, 8) = 'PORTUGUES') THEN (AVG(IFNULL(pqas.fraction, 0)) * 10) ELSE NULL END AS MediaPT,
    CASE WHEN (SUBSTR(mqc.name, 8) = 'MATEMATICA') THEN (AVG(IFNULL(mqas.fraction, 0)) * 10) ELSE NULL END AS MediaMT,
    gg.finalgrade AS NotaGeral,
    u.firstname AS Aluno,
    u.username AS Usuario,
    u.lastname AS siem,
--  u.id, -- removido
    u.department AS Turma,
    u.institution AS Escola,
    pqc.name AS catp,
    mqc.name AS catm

FROM mdl_user u
-- join demais tabelas

WHERE SUBSTR(pqc.name, 8) = 'PORTUGUES'
   OR SUBSTR(mqc.name, 8) = 'MATEMATICA'

GROUP BY turma, u.id
ORDER BY turma ASC, `Aluno` ASC