# Problem with a complex query

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.

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...

Do you mean exactly one occurrence? Or at least one occurrence?

These will (I think) find `No` for "at least":

``````SELECT No  FROM tbl AS m
WHERE  EXISTS ( SELECT 1 FROM tbl WHERE
No = m.No AND  letter IN ('a', 'x', 'y', 'z') )
AND  EXISTS ( SELECT 1 FROM tbl WHERE
No = m.No AND  letter IN ('b', 'w', 'l') )
AND  EXISTS ( SELECT 1 FROM tbl WHERE
No = m.No AND  letter IN ('c', 'k', 'j') )
``````

Another...

``````SELECT No
FROM ( SELECT No, GROUP_CONCAT(letter SEPARATOR '') AS lets
FROM table
GROUP BY No
HAVING lets RLIKE '[axyz]'
AND lets RLIKE '[bwl]'
AND lets RLIKE '[ckj]' ) AS n
``````

(Nothing even close to your invented `IN` syntax is allowed.)