CODEWITHSUNDEEP
sqlsql-server-2000
Deviprasad Das
Deviprasad Das

Reputation: 4363

How to know how many OR conditions are satisfied?

How can i know that which of my result has satisfied how many number of conditions/

SELECT 
    [TITLE] 
FROM 
    [M_TIPS] 
WHERE 
    [TITLE] LIKE '%VALUE%' OR 
    [TITLE] LIKE '%SQL%';

How to know that which of the result has satisfied only 1 OR condition and which of the result has satisfied both conditions. The number of conditions are not static, it can increase.

Can anyone please help ?

Upvotes: 3

Views: 431

Answers (3)

user359040
user359040

Reputation:

Assuming you're using a programming language to dynamically generate the queries with the conditions generated as required, then I suggest generating the query like so:

SELECT [TITLE], SUM([CONDITION_COUNT]) FROM
(SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionString1 UNION ALL
SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionString2 UNION ALL
...
SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionStringN) SQ
GROUP BY [TITLE]

Upvotes: 0

Alex K.
Alex K.

Reputation: 175766

Short circuiting in a WHERE clause *may prevent any match count exceeding 1 if you use OR.

How about storing your match list in a table variable then counting matches;

DECLARE @MATCH TABLE (SCORE INT, TOKEN VARCHAR(16))
INSERT @MATCH 
      SELECT 1, 'VALUE'
UNION SELECT 1, 'SQL'
UNION SELECT 1, 'CAKE'

SELECT 
    [TITLE], SUM(M.SCORE)
FROM 
    [M_TIPS] T INNER JOIN @MATCH M ON T.[TITLE] LIKE '%' + M.TOKEN + '%'
GROUP BY T.[TITLE]

==
sql             1
value           1
xx sql value xx 2

Upvotes: 1

Aaron Digulla
Aaron Digulla

Reputation: 328594

Add that to the result:

SELECT 
    [TITLE] 
    case [TITLE] LIKE '%VALUE%' WHEN true then 1 else 0 end as CONTAINS_VALUE
    case [TITLE] LIKE '%SQL%' WHEN true then 1 else 0 end as CONTAINS_SQL
FROM 
    [M_TIPS] 
WHERE 
    [TITLE] LIKE '%VALUE%' OR 
    [TITLE] LIKE '%SQL%';

Upvotes: 3

Related Questions