CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36611

Using IN and EXISTS subquery with AND operation instead of OR?

create table policies
(
  policyno varchar(15)
);

insert into policies values ('500'),('501'),('502'),('503');

Declare @PolicyNos varchar(1500) = '500|503';    

SELECT * FROM policies
WHERE policyno = ALL(SELECT item from dbo.fnSplit(@PolicyNos,'|'));

User is going to input multiple policies from front end and application sending multiple policies pipe separated as in @PolicyNos variable.

I'm using split function provided here https://stackoverflow.com/a/337752/435559 to check if all policies in @PolicyNos must exists then return true if any one exists but not the other then return false.

I Tried IN and EXISTS but they do OR operation inside but I want AND every policy exists then return true else false.

Edit -- Added In exists code which I tried

SELECT * FROM policies
WHERE policyno IN (SELECT item from dbo.fnSplit(@PolicyNos,'|'));

SELECT * FROM policies
WHERE exists (SELECT item from dbo.fnSplit(@PolicyNos,'|') where item = policies.policyno);

Upvotes: 0

Views: 100

Answers (2)

Dave Manning
Dave Manning

Reputation: 820

I might not have quite understood your question but you could find invalid policy codes with

SELECT item AS invalidPolicy
FROM dbo.fnSplit(@PolicyNos,'|') 
WHERE NOT EXISTS (
    SELECT * 
    FROM policies 
    WHERE item = policies.policyno
)

if you need a conditional

IF EXISTS (
    SELECT item AS invalidPolicy
    FROM dbo.fnSplit(@PolicyNos,'|') 
    WHERE NOT EXISTS (
        SELECT * 
        FROM policies 
        WHERE item = policies.policyno
    )
) BEGIN
     .....
END
ELSE BEGIN
     ......
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can do this using conditional aggregation:

WITH vals(val) as (
      SELECT item
      FROM dbo.fnSplit(@PolicyNos,'|')
     )
SELECT (CASE WHEN COUNT(DISTINCT v.val) = COUNT(DISTINCT p.policyno) THEN 'true'
             ELSE 'false'
        END)
FROM vals v left join
     policies p
     ON v.val = p.policyno

Upvotes: 1

Related Questions