Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

SQL Problem

Codes here !

Moderators: macek, egami, gesf

SQL Problem

Postby ShadowSkill » Fri Mar 01, 2013 9:15 am

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
ShadowSkill
New php-forum User
New php-forum User
 
Posts: 23
Joined: Sun Jan 01, 2012 4:37 am

Re: SQL Problem

Postby seandisanti » Fri Mar 01, 2013 12:08 pm

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'".
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 787
Joined: Mon Oct 01, 2012 12:32 pm

Re: SQL Problem

Postby ShadowSkill » Sat Mar 02, 2013 4:00 am

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.
ShadowSkill
New php-forum User
New php-forum User
 
Posts: 23
Joined: Sun Jan 01, 2012 4:37 am

Re: SQL Problem

Postby seandisanti » Mon Mar 04, 2013 10:16 am

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.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 787
Joined: Mon Oct 01, 2012 12:32 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.