Reputation: 167
How to fill missing rows on one table (A) from another table (B) with MySQL?
I am setting up a virtual classroom in PHP and MySQL. This classroom consists of courses and each course contains different subjects or modules. The student has to examine each module and, finally, a summary (evaluation board) is made to know if the student has passed the course or not.
Having said that, I have a table in which I store the evaluations of each student, in which I keep inscripcion_id (student - inscription_id), modulo_id (module_id), fecha (date_of_examination), aciertos (number_of_right_answers), ultima_convocatoria (evaluation_last_convocatory) and estado (status).
SQL Fiddle -> here
Through some previously established rules that tell me if a student has passed a module or not, I get the following set of data:
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
| inscripcion_id | modulo_id | fecha | aciertos | ultima_convocatoria | estado | ev |
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
| 890 | 1 | 2018-01-24 22:26:09 | 8 | 2 | 1 | aprobado |
| 890 | 2 | 2018-01-24 22:36:58 | 3 | 3 | 0 | suspendido |
| 890 | 5 | 2018-01-24 22:38:50 | 3 | 1 | 0 | suspendido |
| 890 | 6 | 2018-01-24 22:44:20 | 7 | 3 | 0 | suspendido |
| 891 | 1 | 2018-01-25 09:24:42 | 8 | 1 | 1 | aprobado |
| 891 | 2 | 2018-01-25 10:01:55 | 4 | 8 | 0 | suspendido |
| 891 | 4 | 2018-01-25 10:51:49 | 5 | 3 | 1 | suspendido |
| 891 | 5 | 2018-01-25 10:23:45 | 9 | 1 | 1 | aprobado |
| 891 | 6 | 2018-01-25 11:21:20 | 7 | 3 | 0 | suspendido |
| 896 | 1 | 2018-01-25 11:55:48 | 1 | 1 | 1 | suspendido |
| 898 | 1 | 2018-01-25 14:01:51 | 6 | 1 | 1 | suspendido |
| 907 | 1 | 2018-03-25 16:06:18 | 3 | 1 | 0 | suspendido |
| 907 | 2 | 2018-03-25 16:07:34 | 3 | 1 | 0 | suspendido |
| 907 | 3 | 2018-03-25 16:09:04 | 3 | 1 | 0 | suspendido |
| 907 | 4 | 2018-03-25 16:08:13 | 3 | 1 | 0 | suspendido |
| 907 | 5 | 2018-03-25 16:10:37 | 2 | 1 | 0 | suspendido |
| 907 | 6 | 2018-03-25 16:08:44 | 3 | 1 | 0 | suspendido |
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
This data is obtained through the following query:
SELECT e1.inscripcion_id,
e1.modulo_id,
e1.fecha,
e1.aciertos,
e1.convocatoria AS ultima_convocatoria,
e1.estado,
if (
( e1.modulo_id in (SELECT modulo.modulo_id FROM modulo WHERE modulo.curso_id = 1 AND modulo.categoria_id = 1)
AND e1.aciertos <= 7 )
OR ( e1.modulo_id = (SELECT modulo.modulo_id FROM modulo WHERE modulo.curso_id = 1 AND modulo.categoria_id = 2)
AND e1.aciertos <= 11 ),
"suspendido",
"aprobado"
) AS ev
FROM (
SELECT inscripcion_id,
modulo_id,
MAX(convocatoria) AS max_convocatoria
FROM `evaluacion`
GROUP BY inscripcion_id,
modulo_id
ORDER BY `inscripcion_id` ASC,
`modulo_id` ASC,
`convocatoria` ASC
) AS e2
INNER JOIN evaluacion AS e1
ON e1.inscripcion_id = e2.inscripcion_id
AND e1.modulo_id = e2.modulo_id
AND e1.convocatoria = e2.max_convocatoria
As you can see, the student 890, has made modules 1, 2, 5 and 6. What I want to achieve is that the modules that are still pending, I also get as a result in the previous data set. The exemplification:
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
| inscripcion_id | modulo_id | fecha | aciertos | ultima_convocatoria | estado | ev |
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
| 890 | 1 | 2018-01-24 22:26:09 | 8 | 2 | 1 | aprobado |
| 890 | 2 | 2018-01-24 22:36:58 | 3 | 3 | 0 | suspendido |
| 890 | 3 | NULL | NULL | NULL | NULL | pendiente |
| 890 | 4 | NULL | NULL | NULL | NULL | pendiente |
| 890 | 5 | 2018-01-24 22:38:50 | 3 | 1 | 0 | suspendido |
| 890 | 6 | 2018-01-24 22:44:20 | 7 | 3 | 0 | suspendido |
| 891 | 1 | 2018-01-25 09:24:42 | 8 | 1 | 1 | aprobado |
| 891 | 2 | 2018-01-25 10:01:55 | 4 | 8 | 0 | suspendido |
| 891 | 3 | NULL | NULL | NULL | NULL | pendiente |
| 891 | 4 | 2018-01-25 10:51:49 | 5 | 3 | 1 | suspendido |
| 891 | 5 | 2018-01-25 10:23:45 | 9 | 1 | 1 | aprobado |
| 891 | 6 | 2018-01-25 11:21:20 | 7 | 3 | 0 | suspendido |
| 896 | 1 | 2018-01-25 11:55:48 | 1 | 1 | 1 | suspendido |
| 896 | 2 | NULL | NULL | NULL | NULL | pendiente |
| 896 | 3 | NULL | NULL | NULL | NULL | pendiente |
| 896 | 4 | NULL | NULL | NULL | NULL | pendiente |
| 896 | 5 | NULL | NULL | NULL | NULL | pendiente |
| 896 | 6 | NULL | NULL | NULL | NULL | pendiente |
| ... | | | | | | |
+----------------+-----------+---------------------+----------+---------------------+--------+------------+
The result is that the modules that the student has not yet done have been added, with the new value "pending" for the ev column.
I have no idea how to do this ... I've tried, I searched the internet and nothing :(
What is the final objective? What I want is to obtain a final list with all those students who have the pending course (that is, they have some pending module/s), to send them a reminder email that they have to examine themselves of the remaining modules. To those who have approved or suspended, no email will be sent to them.
Can you help me?
SQL Fiddle -> here
THANK YOU VERY MUCH
Upvotes: 1
Views: 70
Answers (2)
Reputation: 1213
After reviewing the new sqlfiddle I wrote the query below, I think it should cover what you want
Notice that you more than one evaluation per module, meaning that you'll get more than one status per module
To solve that, you can add a group statement (in comment now) Or you the different evaluations to your needs...
SELECT
i.inscripcion_id,
c.curso_id,
c.titulo AS curso_titulo,
m.modulo_id,
m.titulo AS modulo_titulo,
IFNULL(ev.estado,0) AS estado,
m.*
FROM
inscripcion i
INNER JOIN curso c ON c.curso_id = i.curso_id
INNER JOIN modulo m ON m.curso_id = c.curso_id
LEFT JOIN evaluacion ev ON ev.modulo_id = m.modulo_id
WHERE
(ev.estado = 0 OR ev.estado IS NULL)
/*
GROUP BY
m.modulo_id
*/
;
Upvotes: 0
Reputation: 8114
Suppose you want to get students with no module in evaluation table as 'pending' like what you provided in the example. The way to get student joining all module is to do a full join on modulo and evaluacion to get full set of distinct inscripcion_id modulo_id. Then left join with your existing query will provide the result you want.
SELECT fs.inscripcion_id,
fs.modulo_id,
e3.fecha,
e3.aciertos,
e3.ultima_convocatoria,
e3.estado,
IF(e3.ev IS NULL, "pendiente", e3.ev) AS ev
FROM (SELECT m.modulo_id,
e.inscripcion_id
FROM modulo m,
evaluacion e
GROUP BY m.modulo_id,
e.inscripcion_id) AS fs
LEFT JOIN (SELECT
e1.inscripcion_id,
e1.modulo_id,
e1.fecha,
e1.aciertos,
e1.convocatoria
AS
ultima_convocatoria
,
e1.estado,
IF (( e1.modulo_id IN (SELECT modulo.modulo_id
FROM modulo
WHERE modulo.curso_id = 1
AND modulo.categoria_id =
1)
AND e1.aciertos <= 7 )
OR ( e1.modulo_id = (SELECT modulo.modulo_id
FROM modulo
WHERE
modulo.curso_id = 1
AND modulo.categoria_id = 2)
AND e1.aciertos <= 11 ), "suspendido",
"aprobado")
AS ev
FROM (SELECT inscripcion_id,
modulo_id,
Max(convocatoria) AS max_convocatoria
FROM `evaluacion`
GROUP BY inscripcion_id,
modulo_id
ORDER BY `inscripcion_id` ASC,
`modulo_id` ASC,
`convocatoria` ASC) AS e2
INNER JOIN evaluacion AS e1
ON e1.inscripcion_id = e2.inscripcion_id
AND e1.modulo_id = e2.modulo_id
AND e1.convocatoria = e2.max_convocatoria)
AS e3
ON fs.modulo_id = e3.modulo_id
AND fs.inscripcion_id = e3.inscripcion_id
ORDER BY fs.inscripcion_id,
fs.modulo_id;
For the further question, You may want to use
SELECT inscripcion_id,
SUM(case when ev = 'aprobado' then 1 else 0 end) as approved_cnt,
SUM(case when ev = 'suspendido' then 1 else 0 end) as suspended_cnt,
SUM(case when ev = 'pendiente' then 1 else 0 end) as pending_cnt
From --the above query...
Group by inscripcion_id
to get the count of status for each student, and then do the logic using those count.
Upvotes: 1
Related Questions
- Query: How to get data from another row to fill lacking data?
- Insert missing records from one table to another using mysql
- Combine two tables and fill in missing values in mysql
- SQL Query to find missing records between two tables and then update the second with the missing records from the first
- SQL: Joining table A and table B when rows may be missing in TABLE B
- How to fill missing values in MySQL query
- MySQL join two tables when one table is missing a row
- mysql: Select from table1 rows that are missing in table2
- query which creates missing rows based on anther table
- query to obtain the missing content between two tables