Repeat condition in WHERE at query time

Question

Repeat condition in WHERE at query time

Navigation

#1 by (0 votes)
#2 by (-2 votes)

0

I currently use WHERE coluna IN (1,2,3,4) in my query. Only I noticed that IN works as a sort of "OR", this gives me a small problem because ID are characteristics of a product. These features are selected from a menu, and I step 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 looks like this:

MARCA
    Volks 1 ITENS

COR
    AZUL 1 ITEM

Well, this is exactly the type of filter I want to do, the problem comes now, because if I click on VOLKS , I will therefore have two filters, "COLOR: Blue" and "MARK: 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

But because of the IN, it looks like it does a type of "OR" in the id of the features, even with two parameters the result returns to:

MARCA
    Volks 2 ITENS

COR
    BRANCA 1 ITEM
    AZUL 1 ITEM

Instead of using IN

It would be possible to use

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

mysql sql where

asked by anonymous 10.02.2017 / 23:03

2 answers

Laravel, Hash :: check (), always returns false Friendly URL whose ID is the news headline [duplicate]

score 0 · Answer 1

One solution is to count the% response_requirements of your 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 subquery and started counting the number of records, which should match the number of features you're running.

Not the best solution, but I think to do it the right way you would have to create these features as columns. It would, for example, have a color column, a column mark, etc ...

score -2 · Answer 2

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