sql relational divide selecting all entries with a column that have all values in a list

Question

I have a table like this:

fields1 | field2
----------------
1_______| a_____
1_______| b_____
1_______| c_____
2_______| a_____
2_______| c_____

and a list of field2 [a,b,c].

I need preferable 1 SQL query that select all field1 entries with the follow conditions:

• the list include all fields2 ex: 1 (a,b,c), OR
• all fields2 are included in the list ex: 2 (a,c)

Answer 1

You can use HAVING clause with CASE WHEN:

the list include all fields2 ex: 1 (a,b,c)

SQL Server:

SELECT LEFT(fields1,1) AS result
FROM #tab
GROUP BY LEFT(fields1,1)
HAVING SUM(CASE WHEN field2 LIKE 'a%' THEN 1 END) > 0
   AND SUM(CASE WHEN field2 LIKE 'b%' THEN 1 END) > 0
   AND SUM(CASE WHEN field2 LIKE 'c%' THEN 1 END) > 0
   AND SUM(CASE WHEN field2 LIKE '[^abc]%' THEN 1 ELSE 0 END) = 0; 
   -- add last condition only if 'a....|b....|c....` are allowed

LiveDemo

all fields2 are included in the list ex: 2 (a,c)

SELECT LEFT(fields1,1) AS result
FROM #tab
GROUP BY LEFT(fields1,1)
HAVING SUM(CASE WHEN field2 LIKE 'a%' THEN 1 END) > 0
   AND SUM(CASE WHEN field2 LIKE 'c%' THEN 1 END) > 0
   AND SUM(CASE WHEN field2 LIKE '[^ac]%' THEN 1 ELSE 0 END) = 0;
   -- add last condition only if 'a...|c...' are allowed

LiveDemo2

Another way is to use COUNT DISTINCT:

SELECT LEFT(fields1,1) AS result
FROM #tab
WHERE LEFT(field2, 1) IN ('a','b','c')
GROUP BY LEFT(fields1,1)
HAVING COUNT(DISTINCT LEFT(field2, 1)) = 3
   AND COUNT(CASE WHEN LEFT(field2, 1) NOT IN ('a','b','c') THEN 1 END) = 0
   -- add last condition only if 'a....|b....|c....` are allowed

LiveDemo3