Compare 2 columns of 2 different tables in the Database

Navigation
1

I'm trying to compare 2 columns (difficulty, difficulty_student) of 2 different tables (EXERCISES, ANSWERS) from the Mysql database and create a new column in the table ANSWERS comparing both tables with a VARCHAR data type (YES or NO). If the user has changed the difficulty of the exercise, a ' YES ' should appear in the cell, if it has not been changed, the cell will be ' NO '.

I would like to compare the column ' difficulty ' of the table EXERCISES and ' difficulty_student ' of the table ANSWERS , and store it in ' difficulty_change ' of the ANSWERS table. But the problem is that the column ' dificulty_change ' can not compare values until the user press the ' submit ' button, which is when they enter the value YES or NO in the column ' difficulty_student ' and thus be able to compare it with ' difficulty ' of the table EXERCISES .

These are my 2 tables. 

CREATE TABLE exercises (
    exercise_id INT,
    difficulty INT
    PRIMARY KEY(exercise_id)
);

CREATE TABLE answers(
    exercise_id_fk INT,
    student_id INT,
    difficulty_change VARCHAR(3),
    difficulty_student INT,
    choice_answer INT,
    FOREIGN KEY(exercise_id_fk) REFERENCES exercises(exercise_id)
);

This is what I have tried so far but I do not know which table is saving ' difficulty_change '. I would like to save it in the ANSWERS table. Can you help me? 

SELECT e.difficulty, a.difficulty_student, 
case when e.difficulty = a.difficulty_student then 'NO' else 'YES' 
         END  as difficulty_change
FROM exercises e 
INNER JOIN answers a  on e.exercise_id=a.exercise_id_fk;

Here is an example of how I would like the tables to remain:

I am trying to make a program in which the user has a series of exercises, and each of them has a drop-down of options where the level of difficulty of the exercise appears. By default, the pulldown of each exercise has its assigned difficulty level, but the user can change it if it seems wrong. In this way, change it or not, your answer will be saved in the table of the database.

What I would like to do is compare 2 columns% co_of% from 2 different tables (EXERCISES, ANSWERS) and create a new column in the table ANSWERS by comparing both tables by VARCHAR or BOOLEAN (YES or NO). If the user has changed the difficulty of the exercise, the cell will be 'YES', if he has not changed it, the cell will be 'NO'.

This is what my table looks like right now:

    
asked by ana 15.08.2018 в 11:11
source

2 answers

1

Hello good morning, friend, you can try the following query:

Mysql 

UPDATE answers AS A 
INNER JOIN exercises AS E 
ON A.exercise_id_fk = E.exercise_id
SET A.difficulty_change = CASE WHEN E.difficulty = A.difficulty_student 
    THEN 'NO' 
    ELSE 'YES' 
    END
WHERE A.student_id = @IDESTUDIANTE

SQLServer 

UPDATE A 
SET A.difficulty_change = CASE WHEN E.difficulty = A.difficulty_student 
    THEN 'NO' 
    ELSE 'YES' 
    END
FROM answers AS A
INNER JOIN exercises AS E 
ON A.exercise_id_fk = E.exercise_id
WHERE A.student_id = @IDESTUDIANTE

I hope you serve the flaw is that through a select you can not insert data into a table, you should use a update with inner join . I realized that you are not filtering by student id @IDESTUDIANTE you should take into account that so you do not change all the records. Add the syntax in SQLServer for those who are interested.

    
answered by 15.08.2018 / 16:34
source
0

The query that you show, definitely does what you want ... now, the only thing you have to do is that that result is recorded in the answers table

This is your query: 

SELECT e.difficulty, a.difficulty_student, 
case when e.difficulty = a.difficulty_student then 'NO' else 'YES' 
         END  as difficulty_change
FROM exercises e 
INNER JOIN answers a  on e.exercise_id=a.exercise_id_fk;

and what we need, is a query that updates the Answers table, and changes just that field. 

UPDATE answers aa
SET difficulty_change = (
    Select case when e.difficulty = a.difficulty_student then 'NO' else 'YES' 
FROM exercises e 
INNER JOIN answers a  on e.exercise_id=a.exercise_id_fk
where aa.exercise_id_fk = a.exercise_id_fk)

Take into account a couple of things:

  • I do not see a primary key in your answers table. The previous query may fail because there is no single way to identify the row in the answers table.
  • The above query may become slow for some cases, unless you add more where clauses to find exactly the row you need.
  • The difficulty_change field must already exist in the answers table
answered by 15.08.2018 в 16:13
Send the output of os.system () to a variable How can I take the value of a textarea and that when I store it in a variable, respect the jumps of textarea lines and spaces?