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
SQL Problem
Moderators: egami, macek, gesf
-
- php-forum Fan User
- Posts: 973
- Joined: Mon Oct 01, 2012 12:32 pm
Code: Select all
UPDATE `column_table` SET `Evaluation`= 'Cannot be taken' WHERE `SubjectPrerequisite ` = 'Failed' OR Remarks= 'No Grade'
-
- New php-forum User
- Posts: 23
- Joined: Sun Jan 01, 2012 4:37 am
Code: Select all
UPDATE `column_table` SET `Evaluation`= 'Cannot be taken' WHERE `SubjectPrerequisite ` = 'Failed' OR Remarks= 'No Grade'
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.
-
- php-forum Fan User
- Posts: 973
- Joined: Mon Oct 01, 2012 12:32 pm
I'm sorry about that, I misunderstood the issue. This is actually what you're looking for:
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.
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'