SELECT condition WHERE with multiple values in the same column

Question

I have two tables in my bank and perform JOIN with them. A table is a list of people and the other characteristic list of that person. By logic there is only one person and each person can have several characteristics, so two tables. So when I make one JOIN appears the same person a few times only with its feature on the side. EX:

+-----+---------+----------------+
|  ID |  PESSOA | CARACTERÍSTICA |
+-----+---------+----------------+
|  1  | Carlos  | Alto           |
+-----+---------+----------------+
|  1  | Carlos  | Divertido      |
+-----+---------+----------------+
|  1  | Carlos  | Inteligente    |
+-----+---------+----------------+
|  2  | Iago    | Baixo          |
+-----+---------+----------------+
|  2  | Iago    | Divertido      |
+-----+---------+----------------+
|  2  | Iago    | Esperto        |
+-----+---------+----------------+
|  3  | Artur   | Divertido      |
+-----+---------+----------------+
|  3  | Artur   | Inteligente    |
+-----+---------+----------------+

If I do this SELECT I get an empty result:

SELECT 
    p.*, c.* 
FROM 
    pessoas AS p LEFT JOIN perfil AS c ON p.pid = c.perfil_pessoa 
WHERE 
    c.caracteristica = 'Divertido' AND c.caracteristica = 'Inteligente'

When in fact I would like the following result:

+-----+---------+----------------+
|  ID |  PESSOA | CARACTERÍSTICA |
+-----+---------+----------------+
|  1  | Carlos  | Alto           |
+-----+---------+----------------+
|  1  | Carlos  | Divertido      |
+-----+---------+----------------+
|  1  | Carlos  | Inteligente    |
+-----+---------+----------------+
|  3  | Artur   | Divertido      |
+-----+---------+----------------+
|  3  | Artur   | Inteligente    |
+-----+---------+----------------+

That is, to have as a result, every person who is 'Divertida' and 'Inteligente'.

If in the clause WHERE of SELECT me use IN or OR the result is also not what I hope, by the example given, the SELECT return all results, because all people have as characteristic "Fun":

+-----+---------+----------------+
|  ID |  PESSOA | CARACTERÍSTICA |
+-----+---------+----------------+
|  1  | Carlos  | Alto           |
+-----+---------+----------------+
|  1  | Carlos  | Divertido      |
+-----+---------+----------------+
|  1  | Carlos  | Inteligente    |
+-----+---------+----------------+
|  2  | Iago    | Baixo          |
+-----+---------+----------------+
|  2  | Iago    | Divertido      |
+-----+---------+----------------+
|  2  | Iago    | Esperto        |
+-----+---------+----------------+
|  3  | Artur   | Divertido      |
+-----+---------+----------------+
|  3  | Artur   | Inteligente    |
+-----+---------+----------------+

The purpose is to build filters using people’s characteristics.

Answer 1

You want to bring the person and all its characteristics if they meet the filter (which will be cumulative).

If you are using the version 8 from Mysql, This is gonna be like a walk in the park.

According to the documentation, this version supports Commom Table Expressions.

_{Free translation with own highlights}

One common table Expression (CTE) is a resultset nominee temporary that only exists within the scope of a single sql statement, therefore can be referenced only in it.

The applications and variations of Ctes are numerous. Your case is certainly one of them.

^{Note - Tables and column names have been simplified in the following examples}

See how we could do:

WITH candidatos AS 
 ( 
     SELECT DISTINCT c.id_pessoa 
     FROM caracteristica c
     WHERE c.caracteristica in ('Alto','Magro') // Insere aqui a lista de qualidades procuradas
     GROUP BY c.id_pessoa
     HAVING count(Distinct c.caracteristica) >= 2 // Aqui a quantidade de qualidades filtradas
 )
SELECT p.nome, c.caracteristica
FROM candidatos filtro 
    JOIN pessoa p ON o.id = filtro.id_pessoa
    JOIN característica c ON c.id_pessoa = p.id

^{Unfortunately I couldn’t find a Mysql 8 fiddle online to enrich the answer with a verifiable example}

It seems a consultation clean enough for me and I really hope you can alplicate such a solution in your scenario for the good of all and the nation’s general happiness.

On the other hand...

If you are using a Mysql version < 8.0

If you are using previous versions, I was able to reach some alternatives that might solve your problem, but continue in search of better alternatives is recommended.

Option 1 - Using some nested selects:

SELECT P.nome, C.caracteristica
FROM PESSOA P
    JOIN CARACTERISTICA C on C.id_pessoa = P.id
WHERE Exists ( SELECT 1
               FROM CARACTERISTICA C2
                    RIGHT JOIN ( SELECT distinct cr.caracteristica
                                 FROM caracteristica cr
                                 WHERE Cr.CARACTERISTICA in ('Alto', 'Magro') ) c1 ON c2.caracteristica = c1.caracteristica
              WHERE c2.id_pessoa = p.id 
              GROUP BY C2.id_pessoa
              HAVING count(c2.caracteristica) >= ( SELECT Count(distinct cr.caracteristica)
                                                   FROM caracteristica cr
                                                   WHERE Cr.CARACTERISTICA in ('Alto', 'Magro') ) )

* There’s still a problem here in the subselects I use a Distinct in the characteristics to obtain a subset with the values Alto and Magro. If you included as a filter a feature that no one had (Feio, for example), this condition would simply be 'ignored' in the result. I think this would not be the expected/ideal behavior.

* This example is available in SQL Fiddle

Option 2 - -Using temporary table:

Another way would be to use a temporary table and insert into it the desired characteristics. The consultation would be less ugly (but still far from ideal), so:

SELECT P.nome, C.caracteristica
FROM PESSOA P
  JOIN CARACTERISTICA C on C.id_pessoa = P.id
  JOIN ( SELECT c2.id_pessoa, count(c2.caracteristica)
         FROM filtro f
             LEFT JOIN caracteristica c2 on c2.caracteristica = f.caracteristica
         GROUP BY c2.id_pessoa
         HAVING COUNT(c2.caracteristica) = (SELECT COUNT(*) FROM filtro) ) pf on pf.id_pessoa = p.id

* This example is also available in SQL Fiddle

About the results you had in your attempt

When you declare the way you had done (No Where of the consultation itself c.caracteristica = 'Divertido' AND c.caracteristica = 'Inteligente') you were getting the empty result because each feature had to be equal to 'Smart'' And equal to 'Fun', which is conceptually impossible, because the feature will always be one or another.

Using IN or OR (also in the Where of the consultation itself, as was suggested) you would end up bringing only the characteristics filtered and any person who had at least one of them.

I hope I’ve helped.

Answer 2

It has ways and ways of doing, which the best, depends on how the darlings.

For your case I would probably prefer to have a table of characteristics, one of people, and a third to make relationships. Regardless of this, there are some alternatives to your current modeling:

Using JOIN:

This is a relatively simple and objective way - to make a JOIN for each feature:

SELECT 
    a.caracteristica,
    b.caracteristica,
    p.pessoa
FROM 
    perfil AS a
    LEFT JOIN perfil AS b ON a.perfil_pessoa = b.perfil_pessoa
    LEFT JOIN pessoa AS p ON a.perfil_pessoa = p.pid
WHERE 
    a.caracteristica = 'Divertido' AND
    b.caracteristica = 'Inteligente'
;

See working on SQL Fiddle.

Although you need a number of JOINs equivalent to the number of characteristics, the use of LEFT already allows an efficient Planner to cascade away items that do not meet any of the characteristics.

Using SUM, IF and HAVING to filter the item count:

The "secret" here is to compare the SUM with the number of items searched for (kind of inspired by the solution of the colleague @Bruno):

SELECT
  GROUP_CONCAT(caracteristica ORDER BY caracteristica) AS caracteristicas,
  pessoa
FROM
  pessoa
  JOIN perfil ON perfil_pessoa = pid
GROUP BY
  pid
HAVING
  SUM(IF(caracteristica IN ('Divertido','Inteligente'),1,0))=2
;

See working on SQL Fiddle.

But this option has a danger: If there are two 'Fun' entries for the same person, it will not work. Be careful to ensure uniqueness of options in this case (or use a DISTINCT). See the advanced solution below:

"Advanced" version of HAVING:

This version uses the same technique as the previous one, but with bit mathematics, ensuring that all terms are found independent of position, listing order in the search or number of occurrences.

Using the FIND_IN_SET we begin to detect in which position of the list is the term searched, setting a bit using the aggregation function BIT_AND:

SELECT
  GROUP_CONCAT(caracteristica ORDER BY caracteristica) AS caracteristicas,
  pessoa
FROM
  pessoa
  JOIN perfil ON perfil_pessoa = pid
GROUP BY
  pid
HAVING
  BIT_OR(POW(2,FIND_IN_SET(caracteristica, 'Divertido,Inteligente')))&6=6
;

See working on SQL Fiddle.

The most complex part is to determine the &6=6 at the end of the query. The FIND_IN_SET returns values in the range of 0 to the number of SET items passed in string. Zero means "not found".

To set the bits we use the POW(2,...). That is, if nothing is found, the bit will be set 0b00000001. If the first term is found, the bit will be set 0b00000010, and so on.

We happen to want the first and the second terms, which gives the binary value 0b00000110, which is precisely 6. We have to ignore the first bit, as it will be set if a term other than the searched one is returned (and the first bit will not be set if the query find the exact search terms).

Thus, making &6, We get the value 6 with or without the last bit set. Remember: word not found is bit zero (value 1). Term 1 is bit 2 (2 in decimal). Term 2 is bit 3 (4 to decimal). Therefore 2 + 4 = 6.

What if it were 3 terms? Same logic, 2 + 4 + 8 = 14, then the expression of HAVING would be &14=14. If it were 4 terms? 2 + 4 + 8 + 16 = 30, leaving &30=30 in the HAVING.

Do I have to calculate "in hand"? No, just use the following formula:

bitfield = ( 2 ^ num_termos - 1 ) * 2

This can be useful in the client language to generate the query.

Using GROUP_CONCAT:

This query is versatile when generating filters in the sense of not needing to JOINs extra (however, in practice, have several JOINs should not be a real problem in this context):

SELECT
  pessoa, caracteristicas
FROM
  (
    SELECT
      GROUP_CONCAT(caracteristica) AS caracteristicas,
      pessoa
    FROM
      pessoa JOIN perfil ON perfil_pessoa = pid
    GROUP BY
      pid
  ) c
WHERE
  CONCAT(',',caracteristicas,',') LIKE "%,Divertido,%" AND
  CONCAT(',',caracteristicas,',') LIKE "%,Inteligente,%"
;

Realize that the query would be much simpler without the CONCAT(',', but it is a good habit when you seek multiple strings, to prevent a LIKE "%amigo%" take a amigo-da-onça by mistake.

The "advantage" here is that you only touch the WHERE, regardless of the number of features.

The downside is that the search is done with LIKE and does not use indexes.

See working on SQL FIDDLE.

Answer 3

This is a (des)known problem called "Relational Division" (I’m trying to find sources in Portuguese but it’s complicated) in which the goal is to find records that meet a set of criteria. There are already some similar questions here at Sopt, for example

In this particular case, it is intended to identify persons who possess (among others) the attributes "Slim" and "Tall".

Based on the response of Diego Rafael Souza, here is a version with a slightly different behavior.

SELECT C.ID_PESSOA, 
       P.NOME,
       C.CARACTERISTICA
  FROM PESSOA P
 INNER JOIN CARACTERISTICA C
    ON C.ID_PESSOA = P.ID
 WHERE C.ID_PESSOA IN
 (
    SELECT ID_PESSOA
      FROM CARACTERISTICA C
     WHERE CARACTERISTICA IN ('Magro', 'Alto')
     GROUP BY ID_PESSOA
    HAVING COUNT(DISTINCT CARACTERISTICA) = 2
 );

In this case note that, unlike the first two alternatives proposed, only people will be returned who are both "Slim" and "Tall". The main difference is in this instruction:

HAVING count(c2.caracteristica) >= ( SELECT Count(distinct cr.caracteristica)
                                       FROM caracteristica cr
                                      WHERE Cr.CARACTERISTICA in ('Alto', 'Magro') ) )

If in your database there is no person who has the attribute "Slim", will be included in the result set all people who are "Tall" and "Fat", for example.

You can confirm the following fiddle.

In your case, you really need to force the number of features to be 2, in the form HAVING COUNT(DISTINCT CARACTERISTICA) = 2 or, as Diego did in option three, using a temporary table and counting the number of existing records.

If you need a more generic solution, use the temporary table or variable table is a good idea and at the same time should have a good performance:

CREATE TABLE FILTRO(f varchar(30));

INSERT INTO FILTRO(f) VALUES('Alto'),('Magro');

SELECT C.ID_PESSOA, 
       P.NOME,
       C.CARACTERISTICA
  FROM PESSOAS P
 INNER JOIN perfil C
    ON C.ID_PESSOA = P.ID
 WHERE C.ID_PESSOA IN
 (
    SELECT ID_PESSOA
      FROM PERFIL 
     WHERE CARACTERISTICA IN ('Magro', 'Alto')
     GROUP BY ID_PESSOA
    HAVING COUNT(DISTINCT CARACTERISTICA) = (SELECT COUNT(1) FROM FILTRO)
 );

Answer 4

I am posting on this date, only to help those who need a more simplified consultation to meet the same situation.

Single table = person

SQL=

   select  P.nome, P.caracteristica
    from 
       pessoa P
      ,(SELECT id from pessoa where caracteristica='Alto') as C1
      ,(SELECT id from pessoa where caracteristica='Inteligente') as C2
    where 
      P.id=C1.id 
      AND P.id=C2.id
      

Each feature has to be created a new subconsulta Example: ,(SELECT id from pessoa where caracteristica='Divertido') as C3 and in cloister where include AND P.id=C3.id

NEW SQL=

   select  P.nome, P.caracteristica
    from 
       pessoa P
      ,(SELECT id from pessoa where caracteristica='Alto') as C1
      ,(SELECT id from pessoa where caracteristica='Inteligente') as C2
      ,(SELECT id from pessoa where caracteristica='Divertido') as C3
    where 
      P.id=C1.id 
      AND P.id=C2.id
      AND P.id=C3.id

Answer 5

• The absence of the profile table model makes the answer difficult, but:

FROM people AS p LEFT JOIN profile AS c ON p.pid = c.perfil_person

• One of the bugs is just above, c.perfil_pessoa has to be c.id for the id of the p.id to receive the profile id.

see:

FROM people p left Join caracteristica c on c.id = p.id

Where Voce can use IN

FROM people p left Join caracteristica c on c.id = p.id WHERE c.feature IN ('Fun', 'Smart')