CODEWITHSUNDEEP
sql-server-2008caseconditional-statements
Matt
Matt

Reputation: 273

SQL conditional where statement

I've written the following sql statement, but I'm unsure how to write the where statement because it is conditional. So I have:

declare @subject varchar(100)

select surname, forename, result 
from student 
join subject on subject.upn = student.upn 
where...

what I then want to write as sql is:

where
if subject = 'English' then subject=@subject and KS2en=''
or if subject = 'Maths' then subject = @subject and KS2ma = ''
or if subject <> 'Maths' and <> 'English' then subject = @subject and KS2av = ''

I assume this can be done logically or maybe using case, but I'm at a bit of a loss at the moment.

Here are the tables:

Student

UPN | Surname | Forename | KS2en | KS2Ma | KS2av

Subject

UPN | Subject

All are varchar.

Upvotes: 1

Views: 405

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Where restricts the number of rows returned, you want to return a different column value according to your condition.

So instead of using Where you have to include the condition into the select via CASE:

DECLARE @subject VARCHAR(100) 

SELECT surname, 
       forename, 
       subject=@subject, 
       KS2en=CASE 
               WHEN subject = 'Engish' THEN '' 
               ELSE ks2en 
             END, 
       KS2ma=CASE 
               WHEN subject = 'Maths' THEN '' 
               ELSE ks2ma 
             END, 
       KS2av=CASE 
               WHEN subject NOT IN( 'Maths', 'English' ) THEN '' 
               ELSE ks2av 
             END 
FROM   student 
       JOIN subject 
         ON subject.upn = student.upn

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453287

Something like the following would do it.

SELECT surname,
       forename,
       result
FROM   student
       JOIN subject
         ON subject.upn = student.upn
WHERE  subject.subject = @subject
       AND '' = CASE @subject
                  WHEN 'English' THEN KS2en
                  WHEN 'Maths' THEN KS2ma
                  ELSE KS2av
                END

I am assuming subject is not nullable. If it is the above query has slightly different semantics than your pseudo code.

Upvotes: 1

Related Questions