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

Question

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);

@JohnSaunders that is what I tried with ALL in subquery, when I try with ANY it does OR operation. — Vishwanath Dalvi, Nov 26 '14 at 03:19
You say "IN and EXISTS" - show us the queries with "IN and EXISTS". — John Saunders, Nov 26 '14 at 03:20
@JohnSaunders You seems to be a school teacher, added code please check. — Vishwanath Dalvi, Nov 26 '14 at 03:23
Not a school teacher. Just someone spending his time to try to help you, and expecting you to do your part to help yourself. — John Saunders, Nov 26 '14 at 03:24
If possible, I'd recommend changing the interface. SQL Server has two data types *designed* to contain multiple values - XML and tables. Pipe-delimited strings (or e.g. CSV) are just a bodge when better data types already exist. — Damien_The_Unbeliever, Nov 26 '14 at 09:15

score 1 · Answer 1 · answered Nov 26 '14 at 03:24

1

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

answered Nov 26 '14 at 03:24

Gordon Linoff

1,242,037
58
646
786

I'm checking for all elements existence so inner join would be fine I feel, why Left join? – Vishwanath Dalvi Nov 26 '14 at 03:37
@mr_eclair . . . An inner join would filter rows from both tables, so you would not find policies in `vals` that don't match. – Gordon Linoff Nov 26 '14 at 03:49
But then it might need to be a full join, or non-matching policies from the input would be ignored (don't know if they should). And then you would probably also need to compare to `count(*)`. – Andriy M Nov 28 '14 at 18:56

score 0 · Answer 2 · answered Nov 26 '14 at 05:23

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

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

2 Answers2