Help in joining these two Mysql queries

Question

I found the option UNION ALL but the results are shown one below of the other, I would like you to create a column for each discipline

I have these two queries and I would like to put them together, the difference between them is that one has a column called MediaMT and the other MediaPT, there is also a difference between the two in a Where line, where in one is SUBSTR(qc.name,8) = "MATEMATICA" and the other is SUBSTR(qc.name,8) = "PORTUGUES" I would like in the result that these two columns were shown one side by the other, it would be:

Mediapt | Mediamt | Mediageral ...

Follow the darlings:

Query for Mediapt

select avg(IFNULL(fraction, 0))*10 as MediaPT, gg.finalgrade as NotaGeral,        u.firstname as Aluno, u.username as Usuario, u.lastname as siem, qas.userid, u.department as Turma, u.institution as Escola, qc.name as cat

FROM mdl_question_attempt_steps qas

inner join mdl_user u on u.id=qas.userid
INNER JOIN mdl_question_attempts qa ON qa.id=qas.questionattemptid
INNER JOIN mdl_question q On q.id=qa.questionid
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_quiz_slots qs ON qs.questionid=qa.questionid
Inner Join mdl_quiz quiz ON quiz.id=qs.quizid
inner join mdl_question_categories qc ON qc.id=q.category

where substring(u.department,2,1) = 4

 and substr(gi.itemname,1)= 4
 and substr(gi.itemname,-1)= 1

 and substr(quiz.ano,1,4)=2018
 and u.lastname=213

 and substr(quiz.name,1)= 4
 and substr(quiz.name,-1)= 1


 AND itemtype = "mod"
 AND itemmodule = "quiz"
 AND finalgrade != "NULL"

 and qas.state!="todo"
 and qas.state!="complete"
 and aggregationstatus = "used"

 and SUBSTR(qc.name,8) = "PORTUGUES" 


and qs.slot=qa.slot

group by turma, u.id

ORDER BY turma ASC, `Aluno` ASC

Query for Mediamt

select avg(IFNULL(fraction, 0))*10 as MediaMT, gg.finalgrade as NotaGeral,        u.firstname as Aluno, u.username as Usuario, u.lastname as siem, qas.userid, u.department as Turma, u.institution as Escola, qc.name as cat

FROM mdl_question_attempt_steps qas

inner join mdl_user u on u.id=qas.userid
INNER JOIN mdl_question_attempts qa ON qa.id=qas.questionattemptid
INNER JOIN mdl_question q On q.id=qa.questionid
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_quiz_slots qs ON qs.questionid=qa.questionid
Inner Join mdl_quiz quiz ON quiz.id=qs.quizid
inner join mdl_question_categories qc ON qc.id=q.category

where substring(u.department,2,1) = 4

 and substr(gi.itemname,1)= 4
 and substr(gi.itemname,-1)= 1

 and substr(quiz.ano,1,4)=2018
 and u.lastname=213

 and substr(quiz.name,1)= 4
 and substr(quiz.name,-1)= 1


 AND itemtype = "mod"
 AND itemmodule = "quiz"
 AND finalgrade != "NULL"

 and qas.state!="todo"
 and qas.state!="complete"
 and aggregationstatus = "used"

 and SUBSTR(qc.name,8) = "MATEMATICA" 


and qs.slot=qa.slot

group by turma, u.id

ORDER BY turma ASC, `Aluno` ASC

I found the option UNION ALL but the results are shown below each other, I would like you to create a column for each discipline

Answer 1

First, I think the and qs.slot=qa.slot should be moved into the JOIN correspondent.

I’m gonna kick that speaker itemtype, itemmodule, finalgrade and aggregationstatus are on the tables gg and gi. If I miss these kicks, let me know.

This is the graph of tables you have:

         qc --- q
                |
quiz --- qs --- qa --- qas --- u --- gg --- gi

In that graph, the qc is in the first query PORTUGUES and in the second is MATEMATICA. Each result before the GROUP BY, brings exactly one tuple from each of these tables.

What happens is that you will need to bring two tuples of some tables to do the Join, one Portuguese and one math (before the GROUP BY). That way, you’ll have a graph like this:

          pqc --- pq
                  |
pquiz --- pqs --- pqa --- pqas
                          |
                          u --- gg --- gi
                          |
mquiz --- mqs --- mqa --- mqas
                  |
          mqc --- mq

Where the tables starting with p are the questions in Portuguese and starting with m are of the math questions. Again, a tuple of each table of the above graph is chosen before being made the GROUP BY. There are cases where the same physical table appears in two different places in the graph as if it were two different tables.

The query looks like this:

-- 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

-- Tabelas que independem da disciplina.
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`

-- Tabelas para relacionar as tuplas de português.
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

-- Tabelas para relacionar as tuplas de matemática.
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 que não depende de disciplina.
WHERE substring(u.department, 2, 1) = 4
AND substr(gi.itemname, 1) = 4
AND substr(gi.itemname, -1) = 1
AND itemtype = "mod"
AND itemmodule = "quiz"
AND gg.finalgrade != "NULL"
AND aggregationstatus = "used"
AND u.lastname = 213

-- Parte do WHERE para filtrar apenas português nas tabelas p.
AND SUBSTR(pquiz.ano, 1, 4) = 2018
AND SUBSTR(pquiz.name, 1) = 4
AND SUBSTR(pquiz.name, -1) = 1
AND pqas.state != "todo"
AND pqas.state != "complete"
AND SUBSTR(pqc.name, 8) = "PORTUGUES"

-- Parte do WHERE para filtrar apenas matemática nas tabelas m.
AND SUBSTR(mquiz.ano, 1, 4) = 2018
AND SUBSTR(mquiz.name, 1) = 4
AND SUBSTR(mquiz.name, -1) = 1
AND mqas.state != "todo"
AND mqas.state != "complete"
AND SUBSTR(mqc.name, 8) = "MATEMATICA"

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

This approach is not very scalable in case you want results with 30 different disciplines. In this case, I suggest you show how is the modeling of the tables involved to get a deeper answer. Also, some things you put in the clauses WHERE (in particular SUBSTR) strongly suggests that your database needs some sort of restructuring if this is possible.