Mysql – Problem with a complex query

MySQL

I've facing problem with my MySQL database. I know how to process when I want to do many conditions on one same column.

Example of a table :

No | Letter
------------ 
1 | a    
5 | b    
4 | b    
4 | c    
5 | b    
6 | g    
4 | a

Typically, if I want to show the 'No' that has Letter a AND Letter b AND Letter C, this query works well :

 SELECT No 
 FROM table 
 WHERE letter IN ('a', 'b', 'c') 
 GROUP BY No
 HAVING COUNt(*) = 3

But what happens, if in the query, I want to subdivide each letter into many occurences and find the 'No' that could have one occurence of GROUP A(a,x,y,z) AND one occurence of GROUP B(b,w,l) AND one occurence of GROUP C(c,k,j) ? 

 SELECT No 
 FROM table 
 WHERE letter IN ('a OR x OR y OR z' AND 'b OR w OR l' AND 'c OR k OR j') 
 GROUP BY No
 HAVING COUNt(*) = 3

I don't know how to do that and I'm lost.

Best Answer

I want to subdivide each letter into many occurences and find the 'No' that could have one occurence of GROUP A(a,x,y,z) AND one occurence of GROUP B(b,w,l) AND one occurence of GROUP C(c,k,j)

Because of a true and false values are treated as +1 and 0 respectively in the number context, the construction of

WHERE condition
HAVING COUNT(*) operation value

is equal to

HAVING SUM(condition) operation value

This form allows you to check a list of conditions in a HAVING clause. So in your case you can use

SELECT No 
FROM table 
GROUP BY No
HAVING 1 = SUM(letter IN ('a', 'x', 'y', 'z'))
   AND 1 = SUM(letter IN ('b', 'w', 'l'))
   AND 1 = SUM(letter IN ('c', 'k', 'j'))

This form allows you to check more complex conditions. For example if you need not exactly one occurence for a group but at least one, you may write

HAVING 1 <= SUM(letter IN ('a', 'x', 'y', 'z'))

if you need 1 or 2, but not 0 or more than 2, occurences, you may write

HAVING SUM(letter IN ('a', 'x', 'y', 'z')) BETWEEN 1 and 2

If you need, for example, 1 occurence or 4 occurencies, you may write

HAVING SUM(letter IN ('a', 'x', 'y', 'z')) IN (1,4)

And so on...

Related Question