validate resultset has all the options given within WHERE IN clause..??

vikram3.r@tcs.com · #1 (**permalink**) 04-20-10, 00:54

select * from Table where column1 IN (value1, value2, value3)

The above query retrieves the records , for which column1's value is either value1 or value2 or value3. It doesn't matter if any of the 3 options are missing.

Is there a way to validate, the resultset of the query has got value1, value2, value3 records atleast once..??

MCrowley · #2 (**permalink**) 04-20-10, 09:18

You could run a separate query to see how many of each (value1, value2, value3) are in the table (and therefore would be returned by the query above).

pdreyer · #3 (**permalink**) 04-20-10, 10:30

Maybe something like this
Not tested

Code:

select * from table a where column1 IN (value1, value2, value3)
  and exists
    (select 1 from table b 
     where a.xxx=b.xxx
       and column1 IN (value1, value2, value3)
     group by xxx
     having count(distinct column1)=3
    )

Pat Phelan · #4 (**permalink**) 04-20-10, 13:29

Yes.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech