How to group multiple where in sql query?

Question

MySQL

Hello, friends.

I'm making a filter for the output of materials on the site. Each item has a category and a subcategory. The database simply has cat and podcat fields . It is necessary to make a multiple selection, for example, for the user to specify a category, and several subcategories. You can specify several such categories and subcategories at a time, manually adding lines for the filter by clicking on the plus sign. Can simply select a category without subcategories, then all are displayed.

How to group multiple WHERE so that the selection is correct? Simply specifying several ANDs does not work, since subcategories can be the same, in different categories.

I think to use a separate query for each category, but there is probably a more elegant solution.

SELECT * FROM materials WHERE cat=1 AND podcat=10 AND cat=2 AND podcat=11

Answer 1

This is done via OR, and also use parentheses to logically separate expressions

SELECT * FROM materials 
WHERE 
    ( cat=1 AND podcat=10 ) /* podcat 10 в cat 1 */
   OR ( cat=2 AND podcat=11 ) /* или podcat 11 в cat 2 */
   OR ( cat = 3 AND podcat IN(13,111) ) /* или podcat 13, 111 в cat 3 */

If all materials are always sorted into subcategories, that is, they are assigned a unique podcat , then the selection can only be made by the podcat field

SELECT * FROM materials 
WHERE podcat IN(10, 11, 13, 111)