Repeat condition in WHERE at the time of query

Question

Currently use WHERE coluna IN (1,2,3,4) in my consultation. Only I realized that the IN works as a kind of "OR", it generates me a small problem, because the ID are features of a product. These features are selected in a menu, and I pass the id into that IN.

Example: Let’s say I have a menu with the following features:

MARCA
    Volks 2 ITENS

COR
    BRANCA 1 ITEM
    AZUL 1 ITEM

If I select AZUL, the menu is as follows :

MARCA
    Volks 1 ITENS

COR
    AZUL 1 ITEM

Great, after all it is exactly this type of filter that I want to do, the problem comes now, because if I click on VOLKS, consequently I will have two filters, "COLOR: Blue" and "BRAND: Volks", and according to the menu, only one product fits these parameters, it would be like reaffirming the result.

MARCA
    Volks 1 ITENS

COR
    AZUL 1 ITEM

Only because of the IN, it seems that it makes a kind of "OR" in the id of the characteristics, even with two parameters the result is again:

MARCA
    Volks 2 ITENS

COR
    BRANCA 1 ITEM
    AZUL 1 ITEM

Instead of using the IN

It would be possible to use

    coluna = 1
AND
    coluna = 2
AND
    coluna = 3
AND
    coluna = 4

Answer 1

One solution is to count the records of subquery in response to his another question as follows:

SELECT prod.ID,
       prod.NOME
  FROM PRODUTOS prod
 WHERE (SELECT COUNT(rel.ID_CARAC)
          FROM RELACAO rel
         WHERE rel.ID_PROD = prod.ID
           AND rel.ID_CARAC IN (1, 2, 3, 4)) = 4

Note that I removed the EXISTS and I’ve counted the number of records, which should match the number of features that you’re portioning.

It’s not the best solution, but I think to do it the right way you’d have to create these features like columns. It would have, for example, a color column, a brand column, etc...

Answer 2

You can also use concatenation. For example, if you click Mark a string " ... and tag = voks " it is added at the end of the query. So with the two clicks, there would be " and mark = voks and color = blue ". Where can be initialized like "Where 1 = 1"; Queries with IN or Exists degrade performance, depending on the case.