0

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.

1

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.