Compare multiple result sets

anil_kodali · #1 (**permalink**) 08-07-09, 14:31

How to compare multiple result sets with a set of values? Here is the scenario..

My query returns me multiple results(one column of data) and I want compare all the data at once with a set of data. For example my query returns 1,2,3 and I want compare the result set with (1,3), can I do that using a query with out using stored procedures? Obviously using the in clause isn't working(i.e., (1,2,3) in (1,3)), any other alternative.

Thanks,

Stealth_DBA · #2 (**permalink**) 08-07-09, 14:52

anil_kodali, one thing that comes to mind is two IN claues.

Code:

WHERE (
          VAL1 IN(SELECT COL1 FROM table-name WHERE...)
       OR 
          VAL2 IN(SELECT COL1 FROM table-name WHERE...)
      )

I don't know if you want either value (OR) in the list or both values (AND). You can adjust as needed.

You might try writing the SELECT COL1... in a Common Table Expression.

n_i · #3 (**permalink**) 08-07-09, 15:23

Depending on whether you are interested in matching or non-matching rows, you should use the INTERSECT or EXCEPT relational operator.

Internet Services

Web Development

Digital Marketing

Consumer Tech