Doubt in filtering a Query in SQL

3

How can I filter a Query so that "Customers" with more than one phone does not appear, which in this case would be Carlos as follows the image below. I'm breaking my head and I can not find anywhere on the net this command.

From now thank you!

Query concerned:

SELECT      C.IDCLIENTE
        ,   C.NOME
        ,   C.SEXO
        ,   E.BAIRRO
        ,   T.TIPO
        ,   T.NUMERO
FROM        CLIENTE C
INNER JOIN  ENDERECO E ON C.IDCLIENTE = E.ID_CLIENTE
INNER JOIN  TELEFONE T ON C.IDCLIENTE = T.ID_CLIENTE
    
asked by anonymous 04.01.2019 / 09:29

2 answers

3

I think this way you can get what you want:

SELECT      C.IDCLIENTE
        ,   C.NOME
        ,   C.SEXO
        ,   E.BAIRRO
        ,   T.TIPO
        ,   T.NUMERO
FROM        CLIENTE C
INNER JOIN  ENDERECO E ON C.IDCLIENTE = E.ID_CLIENTE
INNER JOIN  TELEFONE T ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN  (
                SELECT      C.IDCLIENTE
                        ,   COUNT(1)
                FROM        CLIENTE     C
                INNER JOIN  TELEFONE    T ON C.IDCLIENTE = T.ID_CLIENTE
                GROUP BY    C.IDCLIENTE
                HAVING      COUNT(1) <= 1
            ) C2 ON C2.IDCLIENTE = C.IDCLIENTE

I did not test in MySQL, but I think the syntax is correct.

    
04.01.2019 / 10:30
3

I think Joao's method should work, but I do not see why doing another inner join , I would do it like this:

SELECT      C.IDCLIENTE
        ,   C.NOME
        ,   C.SEXO
        ,   E.BAIRRO
        ,   T.TIPO
        ,   T.NUMERO
FROM        CLIENTE C
INNER JOIN  ENDERECO E ON C.IDCLIENTE = E.ID_CLIENTE
INNER JOIN  (   SELECT      T1.IDCLIENTE
                        ,   T1.TIPO
                        ,   T1.NUMERO
                FROM        TELEFONE T1
                GROUP BY    T1.IDCLIENTE
                HAVING      COUNT(1) <= 1
            ) T ON T.IDCLIENTE = C.IDCLIENTE

I am making a inner join with what I want from the results of the Phone table, that is, I am interested only in data where there is no more than IDCLIENTE equal in table TELEFONE .

I did not test the code, but I think it is correct. At least the idea of what to do you already have

    
04.01.2019 / 10:47