SQL : finding which clause is making my query returning no answer

user_8233104 0
Viewed 48

My query is basic and look like this :

SELECT ID FROM Table WHERE CRIT1='a' AND CRIT2='b' AND CRIT3='c'

However it sometimes return no value. This is normal because there is no match in the table.

To help my users to find which criteria is too restrictive, I would like to find another query which tell me if it is because of clause CRIT1, CRIT2 or CRIT3 that I have no answer.

Currently, I've done it this way (using pseudo code) :

If ( SELECT ID FROM Table WHERE CRIT1='a'  returns EOF ) 

   Then WrongCriteria="CRIT1" 

Elseif ( SELECT ID FROM Table WHERE CRIT1='a' AND CRIT2='b' returns EOF )

    Then WrongCriteria="CRIT2"

Elseif ( SELECT ID FROM Table WHERE CRIT1='a' AND CRIT2='b' AND CRIT3='c'  returns EOF ) 

  Then WrongCriteria="CRIT3"

It works ... but there are several queries and each of them is very slow due to the poor network response time.

My question is thus : It is possible to do the above pseudo-code in one single SQL query?

sql-server sql
3 Answers

You can combine three queries into one by using SUM on a conditional:

SELECT
    SUM(CASE WHEN CRIT1='a' THEN 1 ELSE 0 END) as CRIT1
,   SUM(CASE WHEN CRIT1='a' AND CRIT2='b' THEN 1 ELSE 0 END) as CRIT2
,   SUM(CASE WHEN CRIT1='a' AND CRIT2='b' AND CRIT3='c' THEN 1 ELSE 0 END) as CRIT3
FROM MyTable

Zero in a column corresponds to the criterion being to restrictive.

Note that this is only a different implementation of your three queries, which "prioritizes" the criteria in a specific way (crit1 then crit2 then crit3). In theory, with three criteria you want to test all individual ones, plus three combinations of pairs, i.e get six counts for these conditions:

  • CRIT1='a'
  • CRIT2='b'
  • CRIT3='c'
  • CRIT1='a' && CRIT2='b'
  • CRIT1='a' && CRIT3='c'
  • CRIT2='b' && CRIT3='c'

The above six counts would give you a full picture of which criteria are too restrictive.

user_3358580

Yes it's possible to do this check in a single query using 'OR' operator. I'm assuming it's only one condition which can be wrong at a time:

SELECT CASE WHEN CRIT1 <> 'a' THEN 'CRIT1'
            WHEN CRIT2 <> 'b' THEN 'CRIT2'
            WHEN CRIT3 <> 'c' THEN 'CRIT3' END AS WrongCriteria
FROM Table WHERE CRIT1<>'a' OR CRIT2<>'b' OR CRIT3<>'c'
user_24502240

To show all combinations of restrictions:

SELECT 
        COALESCE( 'Conditions:'
            + NULLIF(
                    ( CASE WHEN CRIT1 <> 'a' THEN ' CRIT1' ELSE '' END )
                    + ( CASE WHEN CRIT2 <> 'b' THEN ' CRIT2' ELSE '' END )
                    + ( CASE WHEN CRIT3 <> 'c' THEN ' CRIT3' ELSE '' END ),
                '' ),
            'None' ) AS Restrictions
FROM MyTable
user_63052940
Related