SQL Problem

[ShadowSkill]

Hello good day, this is what my Database looks like and my problem is i cannot figure out what SQL Query i should use.
I have already solve the part where my evaluation would become "Done" and "Must Be Taken" and here are my Queries

UPDATE `column_table` SET `Evaluation`= 'Done' WHERE  `Remarks` = 'PASSED'
UPDATE `column_table` SET `Evaluation`= 'Must be taken' WHERE `SubjectPrerequisite ` = 'None' AND `Remarks` = 'FAILED'
UPDATE `column_table` SET `Evaluation`= 'Must be taken' WHERE `SubjectPrerequisite ` = 'None' AND `Remarks` = 'No Grade'

The problem is my last query where it will UPDATE the database to "Cannot be taken" when the SubjectPrerequisite's Remarks = FAILED or Grade = 'No Grade'

column_table
______________________________________________________________________________________
SubjectName | SubjectPrerequisite |   Grade   |     Remarks      |       Evaluation
______________________________________________________________________________________
Fil 1             |     None                   |               |  No Grade        |      Must be taken

Eng 1           |     None                   |  90         | PASSED          |      Done

Eng 2           |     Eng 1                  |  74         |  FAILED           |       Must be taken

Eng 3           |     Eng 2                  |              |  No Grade         |      Cannot be taken

Fil 2             |     Fil 1                    |               |  No Grade        |      Cannot be taken
______________________________________________________________________________________

Please tell me if your having a hard time understanding my explanation guys. Thank you very much

[seandisanti]

Code: Select all

UPDATE `column_table` SET `Evaluation`= 'Cannot be taken' WHERE `SubjectPrerequisite ` = 'Failed' OR Remarks= 'No Grade'

It looks like you were trying to look for a literal string in a numeric field with "Grade - 'No Grade'".

[ShadowSkill]

Code: Select all

UPDATE `column_table` SET `Evaluation`= 'Cannot be taken' WHERE `SubjectPrerequisite ` = 'Failed' OR Remarks= 'No Grade'

This cannot be sir since it will update everything if the remarks = no grade but what if that has no prerequisite.

The problem is i dont quite get how the Evaluation would become "Cannot Be Taken"

If a student didnt get the prerequisite of a subject example is (English 1) then he wouldnt be able to take (English 2)

If English 1 has no Grade or is Failed then English 2 would become a "Cannot be taken" on the evaluation since its Prerequisite is Failed or No Grade.

I just can't quite get how the query would look like sirs.

[seandisanti]

I'm sorry about that, I misunderstood the issue. This is actually what you're looking for:

Code: Select all

UPDATE c1 SET Evaluation = 'Cannot be taken' FROM column_table c1 INNER JOIN column_table c2 on c1.SubjectPrerequisite = c2.SubjectName WHERE c2.Remarks = 'No Grade' or c2.Remarks = 'Failed'

People often forget that there is a FROM clause to every query, even if we don't typically use it. In this case, using the FROM allows you to join the table back on itself to eliminate unnecessary rows, and create the logical relation ship between the subject and the prerequisite.