Help with SQL Query

I have a query that need to filter data based on 4 input parameter supplied to the stored procedure.

The parameters are
      Board_id, class_id, subject_id,exam_category_id

If board_id >-1 and remaining of the parameters are not -1, then
      the board_id criteria in the where clause should be used.

ie if my query is
      SELECT * FROM EXAM_RESULTS_VIEW
      WHERE
            
            SUBJECT_ID                  =      @SUBJECT_ID  AND
            CLASS_ID                  =      @CLASS_ID  AND
            BOARD_ID                  =      @BOARD_ID AND
            EXAM_CATEGORY_ID            =      @EXAM_CATEGORY_ID

Is there a way i could do it to see if the where clause criteria uses only if the value of any parameter is greater than -1

to illustrate it further,
      if class_id=2 and board_id=1 and subject_id=-1 and exam_category_id=-1
then the query should use on the Board_ID and Class_ID to filter the data based on the data supplied.

TECH_NETAsked:
Who is Participating?
 
appariCommented:
try like this

SELECT * FROM EXAM_RESULTS_VIEW
      WHERE
           
            (SUBJECT_ID                  =      @SUBJECT_ID or @SUBJECT_ID = -1) AND
            (CLASS_ID                  =      @CLASS_ID or @CLASS_ID = -1)  AND
            (BOARD_ID                  =      @BOARD_ID or @BOARD_ID = -1)  AND
            (EXAM_CATEGORY_ID            =      @EXAM_CATEGORY_ID or @EXAM_CATEGORY_ID = -1)
0
 
SharathData EngineerCommented:
try like this.


SELECT * FROM EXAM_RESULTS_VIEW
      WHERE 
            
            SUBJECT_ID                  =      case when @SUBJECT_ID > -1 then @SUBJECT_ID else  SUBJECT_ID end AND
            CLASS_ID                  =      case when @CLASS_ID > -1 then @CLASS_ID else CLASS_ID end AND
            BOARD_ID                  =      case when @BOARD_ID > -1 then @BOARD_ID else BOARD_ID end and
            EXAM_CATEGORY_ID            =      case when @EXAM_CATEGORY_ID > -1 then @EXAM_CATEGORY_ID else EXAM_CATEGORY_ID end

Open in new window

0
 
TECH_NETAuthor Commented:
Sharath,

can you explain how your solution is different from that suggested by appari. The solution of appari does work.

I am curious to know which would be good from a performance standpoint.

Appari,

When you use in the where clause
(SUBJECT_ID                  =      @SUBJECT_ID or @SUBJECT_ID = -1)
so what does the subject_id equate to. what i meant is if the subject_id supplied is -1, it would be
(SUBJECT_ID                  =      -1 or @SUBJECT_ID = -1)

I am understanding what is the difference between SUBJECT_ID =-1  and @SUBJECT_ID=1
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
appariCommented:
>>(SUBJECT_ID                  =      -1 or @SUBJECT_ID = -1)
using "or" operator so even if one condition is true the expression evaluates to true for all the records. when @subject_id is -1 it simply ignores the check on Subject_id column.
0
 
SharathData EngineerCommented:
<< Is there a way i could do it to see if the where clause criteria uses only if the value of any parameter is greater than -1

to illustrate it further,
      if class_id=2 and board_id=1 and subject_id=-1 and exam_category_id=-1
then the query should use on the Board_ID and Class_ID to filter the data based on the data supplied. >>

With appari's solution, for the example you mentioned, the query becomes as


SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)
   
appari's query is using all the filters ( your intention is not to user filter on Subject_Id and EXAM_CATEGORY_ID which is not happening in this case)

I am not sure how are you getting your deserved result with appari's query.

My query will become as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE SUBJECT_ID = case when -1 > -1 then -1 else SUBJECT_ID end
   AND CLASS_ID = case when 2 > -1 then 2 else CLASS_ID end
   AND BOARD_ID = case when 1 > -1 then 1 else BOARD_ID end
   and EXAM_CATEGORY_ID = case when -1 > -1 then -1 else EXAM_CATEGORY_ID end

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE SUBJECT_ID = SUBJECT_ID
   AND CLASS_ID = 2
   AND BOARD_ID = 1
   and EXAM_CATEGORY_ID = EXAM_CATEGORY_ID

In my query, filters are applied on only CLASS_ID and BOARD_ID. Comparing SUBJECT_ID with SUBJECT_ID and EXAM_CATEGORY_ID with EXAM_CATEGORY_ID will not effect the end result.

I have attached a sample data. You can test with @col values as 10, -1 , 1 and see the deference between two queries.
declare @table as table (col int)
insert into @table values (10)
insert into @table values (20)
insert into @table values (1)
insert into @table values (-1)
insert into @table values (30)
declare @col int
select @col = 10
select * from @table where (col = @col or col = -1)
select * from @table where col = case when @col > -1 then @col else col end

Open in new window

0
 
TECH_NETAuthor Commented:
Moderator, Sorry i made a mistake in awarding the point. please revert the point to appari. His solutions did work. I did not try out Sharath_123:

0
 
TECH_NETAuthor Commented:
Moderator, could you spli t he point to both appari and sharath_123.

0
 
TECH_NETAuthor Commented:
Thank sharath, your explaination and insight is very good. Thanks a ton for the detailed illustration.

0
 
appariCommented:
**************
SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)
   
appari's query is using all the filters ( your intention is not to user filter on Subject_Id and EXAM_CATEGORY_ID which is not happening in this case)
***********
Sharat_123,
check again,
my query evaluates to this query,

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE -1 = -1
   AND CLASS_ID = 2
   AND BOARD_ID = 1
   AND -1 = -1
0
 
SharathData EngineerCommented:
appari - can you test the sample data attached in my post# 26197951 and comment.
0
 
appariCommented:
SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1 or -1 = -1)
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

which can be further written as

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE (SUBJECT_ID = -1)
   AND (CLASS_ID = 2)  
   AND (BOARD_ID = 1  or 1 = -1)  
   AND (EXAM_CATEGORY_ID = -1 or -1 = -1)

---(SUBJECT_ID = -1 or -1 = -1)  this one always becomes true as -1 = -1 is true so this condition is true irrespective of Subject_ID column value and same with  (EXAM_CATEGORY_ID = -1 or -1 = -1)

you check it yourself.
0
 
appariCommented:
the sql in your comment  after --which can be further written as
part is wrong it evaluates to

SELECT *
  FROM EXAM_RESULTS_VIEW
 WHERE true -- (SUBJECT_ID = -1 or -1 = -1)  ****** as -1 = -1
   AND (CLASS_ID = 2 or 2 = -1)  
   AND (BOARD_ID = 1 or 1 = -1)  
   AND true -- (EXAM_CATEGORY_ID = -1 or -1 = -1) ****** as -1 = -1
0
 
SharathData EngineerCommented:
<< appari - can you test the sample data attached in my post# 26197951 and comment.>>

for the sample set, i provided, getting different results with both queries. Did you get a chance to check that one?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.