How to make an "in" statement inclusive-only in SQL

Question

EDITED:
Read table_row as table_column (Dudu Markovitz)

I have a subset of values that I want to evaluate against a row in SQL.

Select count(*) cnt  
from table_name c
where c.table_row in ('value 1','value 2', 'value 3')
and other statements;

This works fine as long as I only want to determine if "table_row" contains one of those three values.

What I want, however, is to determine if table_row contains only those values.

Is there any SQL statement that can perform an inclusive-only 'in' statement, so that for the "cnt" in the above query, I will only get a result if 'table_row' is in the set indicated by the query?

Are you saying where 1 row contains all 3 values, or any of the 3 values exist in any row? — CodeLikeBeaker, Dec 21 '16 at 21:42
That is not logical. You cant check that a value is the same as all the values in a range. To illustrate lets assume a record has the value `value 1` in the column `table_row`, how could it then also be `value 2` and `value 3`. There is always but one value in a record's column. If you think there are multiple values then you are no longer thinking of a column but of a linked table. — Igor, Dec 21 '16 at 21:49
Your question is nonsense. What you are asking for is exactly what the `IN` condition does. As @PM77-1 suggests, please provide sample data and desired results. — JNevill, Dec 21 '16 at 21:50

Sebas · Answer 1 · 2016-12-22T10:56:51.920

0

Maybe this would do?

select count(*) cnt  
from table_name c
where
not exists (
    select * table_name t
    where COALESCE(table_row,'-1') != 'value 1' 
    and COALESCE(table_row,'-1') != 'value 2' 
    and COALESCE(table_row,'-1') != 'value 3'
)
and other statements;

It looks like of weird as the subquery is not linked to the parent query but... It should work.

edited Dec 22 '16 at 10:56

answered Dec 21 '16 at 21:46

Sebas

21,192
9
55
109

Almost... :-) what if table_row can be NULL? – David דודו Markovitz Dec 22 '16 at 05:01
Updated... meny ugly – Sebas Dec 22 '16 at 09:38
Fair, but not bullet proof. In Oracle for example empty string is treated as NULL – David דודו Markovitz Dec 22 '16 at 09:40
God, I worked with mysql for too long, you're absolutely right – Sebas Dec 22 '16 at 10:56

score 0 · Answer 2 · answered Dec 21 '16 at 21:51

0

You can use Having clause. Below is a pseudocode.

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
GROUP BY LastName
HAVING LastName in ('Davolio','Fuller')

answered Dec 21 '16 at 21:51

Baskar Rao

470
4
11

2

This is, functionally, the exact same thing. – JNevill Dec 21 '16 at 21:52

David דודו Markovitz · Answer 3 · 2016-12-22T06:43:29.023

This is what you might looking for -
Show the result of the count only if contains no other value then 'value 1', 'value 2' and 'value 3'

select  count(*) as cnt  
from    table_name c
where   c.mycol in ('value 1','value 2', 'value 3')
        -- ...and other statements
having  count(*) = 
        count(case when c.table_row in ('value 1','value 2', 'value 3') then 1 end)

And you might also just want to see the counts

select  count(*)                                                                    as cnt  
       ,count(case when c.table_row in ('value 1','value 2', 'value 3') then 1 end) as cnt_val
from    table_name c
where   c.mycol in ('value 1','value 2', 'value 3')
        -- ...and other statements

How to make an "in" statement inclusive-only in SQL

3 Answers3