Doubt about filtering a Query in SQL

Asked

Viewed 66 times

2

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

Thank you in advance!

inserir a descrição da imagem aqui

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

2 answers

3

I think Joao’s method should work, but I don’t see why to make another inner join, I’d 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’m making a inner join with what I want from the results of the table Phone, ie, I am interested only the data where there is no more than one IDCLIENTE equal in table TELEFONE.

I haven’t tested the code, but I think it’s correct. At least the idea of what to do you already have

2


I think that in this way you will be able to achieve 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 haven’t tried it in Mysql, but I think the syntax is correct.

  • It worked buddy, thank you very much, I have to study more because there are some things you did that I haven’t studied yet. Thank you very much.

  • Ola Paulo, this method of Joao, although it works does not have a good performance, due to the amount of joins made that could not exist.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.