TECH_NET
asked on
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_i
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.
The parameters are
Board_id, class_id, subject_id,exam_category_i
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>>(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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
ASKER
Moderator, could you spli t he point to both appari and sharath_123.
ASKER
Thank sharath, your explaination and insight is very good. Thanks a ton for the detailed illustration.
**************
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
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
appari - can you test the sample data attached in my post# 26197951 and comment.
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.
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.
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
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
<< 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?
for the sample set, i provided, getting different results with both queries. Did you get a chance to check that one?
Open in new window