Set query sql according to available discipline

Navigation
0

I have this query, working fine, but it works only if I have data for the two disciplines PORTUGUESE and MATHEMATICS, how can I do that, without harming the result, regardless of whether there are only one or both disciplines I can get the results, it 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
    
asked by anonymous 27.08.2018 / 14:49

2 answers

1

Response changed after comments

Using validation via OR (since the control fields are from different tables), also add a validation in the field query; if it exists (Portuguese or mathematics), calculate the average.
This validation is required to display the two averages (if any) or just 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
    
27.08.2018 / 15:03
2

How to do

You can use the 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

Line: 

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"

    
27.08.2018 / 14:55
Effect appears without creating scrollbar What is the lowest direct query weight or using views for a double relationship?