CODEWITHSUNDEEP
mysqldatabase
ana
ana

Reputation: 417

Compare 2 columns from different tables in database

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

These are my 2 tables. I want to compare ‘difficulty’ from table EXERCISES and ‘difficulty_student’ from table ANSWERS, and store it in ‘difficulty_chage’ in table ANSWERS.

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

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

This is what I have tried but doesn’t work, can you help me?

SELECT exercises.difficulty, answers.difficulty_student, 
       IF(difficulty = difficulty_student, NO) as difficulty_change
FROM answers
ELSE 'YES' as difficulty_change FROM answers

This is what I would like it to be:

enter image description here

Upvotes: 0

Views: 84

Answers (2)

Estreiten
Estreiten

Reputation: 56

If you want to persist the changes on the DB, you could use an update statement:

update answers 
inner join exercises on answers.exercise_id_fk = exercises.exercise_id 
set answers.difficulty_change = 'YES' 
where answers.difficulty_student != exercises.difficulty;

Note: the difficulty_change column should be a VARCHAR(3) for the answers table to work with values 'YES' or 'NO'. I'm assuming your answers table has by default the 'NO' value on the difficulty_change column.

Here's the schema and data I used to test the query:

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

insert into exercises values (1,1);
insert into exercises values (2,2);
insert into exercises values (3,3);
insert into exercises values (4,4);
insert into exercises values (5,5);

insert into answers values (1,1,'NO',1,1);
insert into answers values (2,1,'NO',2,1);
insert into answers values (3,1,'NO',3,1);
insert into answers values (4,1,'NO',5,1);
insert into answers values (5,1,'NO',4,1);

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

In you pricture there is not a table user but exercise and answer so
You could join the table (assuming by relation based on exercise_id) 

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 answer a  on e.exercise_id=a.exercise_id_fk

for change the column difficulty_change in table answer you should use an updated ... but you have int in your difficulty_change column so or you use a numeric result for query eg: 

UPDATE answer a 
INNER JOIN exercises e  on e.exercise_id=a.exercise_id_fk
set a.difficulty_change =   case when e.difficulty = a.difficulty_student then 0 else 1 END
where e.exercise_id=a.exercise_id_fk

or you must change the data typr for difficulty_change as varchar(3) and use 

UPDATE answer a 
INNER JOIN exercises e  on e.exercise_id=a.exercise_id_fk 
set a.difficulty_change = case 
     when e.difficulty = a.difficulty_student then 'NO' else 'YES' END
where e.exercise_id=a.exercise_id_fk

Upvotes: 2

Related Questions