3

In this query I want to select rows from Question table where Subject Code is X if not then Go for Y and so on as mentioned in the case statement.

select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
inner join Answer mclo 
on         mclo.AnswerID = mcl.AnswereID
where      StudentID = 6588
and        mclo.SubjectCode = (case when mclo.SubjectCode ='X' then 'X' 
                               else 
                                 case when mclo.SubjectCode ='Y' then 'Y' 
                                 else 
                                   case when mclo.SubjectCode ='Z' then 'Z' 
                                   else 
                                     case when mclo.SubjectCode ='P' then 'P' 
                                     end  
                                   end 
                                 end 
                               end)

closed as unclear what you're asking by ypercubeᵀᴹ, Erik Darling, Mr.Brownstone, Colin 't Hart, mustaccio Feb 2 '18 at 16:09

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I think you need to update your conditions – Gour Gopal Feb 2 '18 at 7:44
  • 1
    It's not clear what you want to achieve with this case expression. The whole WHERE you have is equivalent to where StudentID = 6588 and mclo.SubjectCode IN ('X', 'Y', 'Z', 'P') – ypercubeᵀᴹ Feb 2 '18 at 9:07
  • Seems clear to me. He wants values from the question table for the most significant value of Subjectcode that exists within that table for that student. – Paul Feb 5 '18 at 8:17
3

as fas as I understand you would like to control the order of execution if x is not present search for y and so on. Therefore a simple IN clause or the case expressions probably won't work if you've got entries for x as well as for y and z....by result returning multiple rows.

The best way I can think of would be to introduce a priority factor within a cte and use this to return only the relevant row.

Here is an example. For simplicity I use a table with the following structure

CREATE TABLE test ( StudentID int, SubjectCode char(1) );

The table has the following entries: enter image description here

As you described we'd like to return X first, then possibly Y (if no X present) and at last Z and only one row per student if the applicable subject code.

XYZ is easy because it is alphabetical but let's introduce some additional complexity guessing that there could be also an A which we would like to return if there is no X.

Here is a SQL Fiddle with my example

I would use the following query to achieve this:

    WITH 
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('A', 2), ('Y', 3), ('Z', 4)
        ) AS tab(code,prio)
  )
 SELECT StudentId, SubjectCode
 FROM
 (
 SELECT 
   StudentId,
   SubjectCode,
   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn
 FROM test t
 INNER JOIN cteSortLogic  sl ON t.SubjectCode = sl.code
 WHERE SubjectCode IN ('X', 'Y', 'Z')
 ) res
 WHERE res.rn = 1
;

The CTE gives me a simple table with the codes and their priority of output: enter image description here

Then I join it to my table and add a rownumber based on the priority: So here's the output of my subselect (or derived query) named "res": enter image description here

I have still got all the rows but assigned a rownumber based on my priority. Then the last thing I have to do is to filter only the most relevant rows which are the one having row number 1 .

Presto...here's the result of the whole query:

enter image description here

So given your example query your code would look like this:

    WITH 
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('Y', 2), ('Z', 3)
        ) AS tab(code,prio)
  )
SELECT *
FROM
(
select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn, *
FROM       dbo.Question mcl 
inner join Answer mclo on         mclo.AnswerID = mcl.AnswereID
inner join cteSortLogic sl on mclo.SubjectCode = sl.code
where      StudentID = 6588
AND         SubjectCode IN ('X', 'Y', 'Z')
)
WHERE
    rn = 1
1

You can find a related question here:

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Having a look at CASE on MS-Docs about :

Return Values

Simple CASE expression:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

  • Allows only an equality check.

  • In the order specified, evaluates input_expression = when_expression for each WHEN clause.

  • Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE expression:

  • Evaluates, in the order specified, Boolean_expression for each WHEN clause.

  • Returns result_expression of the first Boolean_expression that evaluates to TRUE.

  • If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

You could rewrite your query as:

SELECT     dbo.GetStudentNumber('M', mcl.StudentID) AS Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
INNER JOIN Answer mclo 
ON         mclo.AnswerID = mcl.AnswereID
WHERE      StudentID = 6588
AND        mclo.SubjectCode = CASE mclo.SubjectCode WHEN 'X' THEN 'X'
                                                    WHEN 'Y' THEN 'Y'
                                                    WHEN 'Z' THEN 'Z'
                                                    WHEN 'P' THEN 'P' END;
DECLARE @TEST INT;
SET @TEST = 1;

WHILE @TEST <= 5
BEGIN

    SELECT CASE @TEST WHEN 1 THEN 100
                      WHEN 2 THEN 200
                      WHEN 3 THEN 300
                      WHEN 4 THEN 400
                      ELSE 500 END TEST;

   SET @TEST += 1;                      
END;
GO
| TEST |
| ---: |
|  100 |

| TEST |
| ---: |
|  200 |

| TEST |
| ---: |
|  300 |

| TEST |
| ---: |
|  400 |

| TEST |
| ---: |
|  500 |

dbfiddle here

  • Isn't that and clause basically evaluating to "when SubjectCode = SubjectCode" ? – Paul Feb 2 '18 at 11:29
0

You could do it something like this:

SELECT     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number, 
           SubjectCode,*
FROM       dbo.Question mcl 
inner join Answer mclo on mclo.AnswerID = mcl.AnswereID
INNER JOIN (SELECT TOP 1 subjectcode, studentid FROM Question ORDER BY CASE WHEN subjectcode = 'X' THEN 0
                WHEN subjectcode = 'Y' THEN 1
                WHEN subjectcode = 'Z' THEN 2
                WHEN subjectcode = 'P' THEN 3
                ELSE 10
                END) AS bestCode ON mcl.studentid = bestCode.studentid 
where      StudentID = 6588
and        mclo.SubjectCode = bestCode.SubjectCode

Where by the subquery bestCode finds the most significant subjectcode for each studentid.

  • I suspect you need CROSS APPLY in that subquery instead of INNER JOIN. – ypercubeᵀᴹ Feb 2 '18 at 12:25
  • i don't believe so, tho the 2nd element of where clause could be put on the join instead – Paul Feb 2 '18 at 13:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.