SQL with 2 of several conditions

Question

SQL with 2 of several conditions

Navigation

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

2

I'm having trouble making a Select that meets the 2 or 3 conditions of several selected, for example: Conditions:

- Cidade Tal (São Paulo)
- Nome Tal (João)
- Sexo (Masculino)
- Fumante (Não)
- Carro Próprio (Sim)

I want everyone to meet with TWO or THREE of the conditions, depending on the moment I want them to meet TWO, and at other times I want 3 of them, but I would like a solution other than making combinations of OR and AND. ..

other than form: ((Cidade AND Nome) OR (Cidade AND Sexo) OR (Cidade AND Fumante) OR (Cidade AND Carro) OR (Nome AND Sexo) OR (Nome AND Fumante) OR (Nome AND Carro) OR (Sexo AND Fumante) OR (Sexo AND Carro) OR (Fumante AND Carro))

And the thing gets worse with more options and when I want 3 questions instead of 2 ....

------------ Tabela do Banco ------------
ID Nome  Estado Sexo Fuma Transporte Idade
1  Joao  RJ     Masc Sim  Sim        35
2  Maria SC     Fem  Sim  Sim        27
3  Jose  RJ     Masc Nao  Sim        23
4  Ana   SP     Fem  Nao  Nao        19
5  Carla SP     Fem  Nao  Sim        26
6  Bia   RJ     Fem  Nao  Nao        39
7  Edu   RJ     Masc Sim  Nao        18
8  Fabio SC     Masc Nao  Sim        31
...

I would like a result that only brings me who meets the 3 of the specification: Estado: RJ Sexo: Feminino Fuma: Nao Transporte: Sim Idade: >30

------------ Tabela Resultado ------------
ID Nome  Estado Sexo Fuma Transporte Idade
1  Joao  RJ     Masc Sim  Sim        35 (Estado: RJ & Transporte: Sim & idade >30)
3  Jose  RJ     Masc Nao  Sim        23 (Estado: RJ & Fuma: Nao & Transporte: Sim)
5  Carla SP     Fem  Nao  Sim        26 (Sexo: Fem & Fuma: Nao & Transporte: Sim)
6  Bia   RJ     Fem  Nao  Nao        39 (Estado: RJ & Sexo: Fem & Fuma: Nao & Idade >30 "essa atende a 4 dos requisitos")
8  Fabio SC     Masc Nao  Sim        31 (Fuma: Nao & Transporte: Sim & Idade >30)
...

Any light at the end of the tunnel?

php mysql sql select

asked by anonymous 20.02.2017 / 22:42

2 answers

0

Well, you can concatenate your query.

example:

$query = "SELECT * from table WHERE 1 = 1";
if ($nome)
{
    $query .= "Nome = ". $nome;
}
if ($cidade)
{
    $query .= "Cidade = ". $cidade;
}

This is just an example. I suggest using PDO for security reasons. :)

20.02.2017 / 22:59

Invert values from one vector to another vector How to change Graphics2d objects out paintComponent

score 1 · Accepted Answer

You can create a temporary table and go by counting how many records meet each condition.

DROP TEMPORARY TABLE IF EXISTS 'tmp_condicao';
CREATE TEMPORARY TABLE tmp_condicao(
  id INT NOT NULL primary key,
  qtd INT NOT NULL
);

INSERT INTO tmp_condicao(id, qtd)
SELECT
  id,
  0
FROM minhaTabela;

UPDATE tmp_condicao AS A
INNER JOIN minhaTabela AS B
  ON (A.id = B.id)
SET A.qtd = A.qtd + 1
WHERE B.estado = 'RJ';

SELECT
*
FROM tmp_condicao;

For each new condition you have, simply repeat the UPDATE and add the WHERE that you want to add.

Finally, to search for records that have 3 conditions or more you can do:

SELECT
B.*
FROM tmp_condicao AS A
INNER JOIN minhaTabela AS B
  ON (A.id = B.id)
WHERE A.qtd >= 3;