CODEWITHSUNDEEP
mysqlsql-server
user1314404
user1314404

Reputation: 1295

How to put condition in sql query

I want to show all scored records in score table and student name in student table if column "flag" equal "shown". Else, if column "flag" in Score table equal "hidden" I will show all scored records with student name = "Student". Finally show all that in order of time.

Here is what I tried:

select s.student_name if h.flag = 'shown', select 'student' as s.student_name if h.flag = 'hidden',
   h.subject1_score,
   h.subject2_score,
   h.subject3_score,
   h.subject4_score,
   h.subject5_score,
   h.exam_date
from Score_Table h
join Student s on h.student_id = u.student_id
order by h.exam_date

But that doesn't seem to work. What should I change? Thank you!

Upvotes: 1

Views: 99

Answers (2)

Mahesh
Mahesh

Reputation: 8892

The cASE i evolves as, 

CASE WHEN h.flag = 'shown'

It will check if the f.flag='shown' if true then it will select the s.studentName that is row value from table. And if the case is false then it will select the student as the StudentName.

You can also use the case as,

select 
     CASE WHEN h.flag = 'shown' THEN  s.student_name
     ELSE 'student' END AS StudentName,
   h.subject1_score,
   h.subject2_score,
   h.subject3_score,
   h.subject4_score,
   h.subject5_score,
   h.exam_date
from Score_Table h
join Student s on h.student_id = u.student_id
order by h.exam_date

Upvotes: 1

Konerak
Konerak

Reputation: 39763

Your IF syntax is off. Read http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

select if(h.flag = 'shown',s.student_name,'student') student_name,
   h.subject1_score,
   h.subject2_score,
   h.subject3_score,
   h.subject4_score,
   h.subject5_score,
   h.exam_date
from Score_Table h
join Student s on h.student_id = s.student_id
order by h.exam_date

Upvotes: 2

Related Questions