SQL search between two tables with duplicate result

Asked

Viewed 106 times

1

I have a View and a table. A View V_CUSTOMER with customer data, but does not have the same CPF/CNPJ as in the view, which is identified by the field V_CUSTOMER.CUSTOMER_ID that makes the relationship with the table BUSINESSDATA_1SAT.CUSTOMER and the countryside ID of the same.

Select
         V_CUSTOMER.CUSTOMER_FIRST_NAME,
         V_CUSTOMER.CUSTOMER_PHONE_2,
         V_CUSTOMER.CUSTOMER_SURNAME,
         V_CUSTOMER.CUSTOMER_TYPE,
         V_CUSTOMER.CUSTOMER_STATUS_ID,
         V_CUSTOMER.CUSTOMER_ID,
         businessdata_1sat.pkg_enc.decrypt_text(businessdata_1sat.customer.reference_number_enc, businessdata_1sat.customer.reference_number_iv) ref_num_CPF_CNPJ
     From
         V_CUSTOMER,
         businessdata_1sat.customer
     Where
         V_CUSTOMER.CUSTOMER_TYPE <> 'Técnico' And
         V_CUSTOMER.CUSTOMER_STATUS In ('Ativo', 'Pendente') And
         V_CUSTOMER.CUSTOMER_PHONE_2 Is Not Null and
         V_CUSTOMER.CUSTOMER_ID IN (Select
            cus.id as customer_id
        From
            BUSINESSDATA_1SAT.CUSTOMER cus
        WHERE
            V_CUSTOMER.CUSTOMER_ID = cus.ID)

In the second Select I can filter the Ids of one with the other.

The result gives me the repeated Customers and multiple CPF with different results and is unique field:

inserir a descrição da imagem aqui

How to resolve this query?

Additional information

Each Customer_id is unique and has a unique CPF. The Follow statement shows me the Customer ID and the CPF of the same, all are unique.

select cus.id as customer_id, businessdata_1sat.pkg_enc.decrypt_text(cus.reference_number_enc, cus.reference_number_iv) ref_num_CPF_CNPJ
from businessdata_1sat.customer cus
where cus.id = 10;

inserir a descrição da imagem aqui

mysql sql query oracle

  • and the ref_num_cpf_cnpj that changes to each line... will put where ?

  • @Rovannlinhalis I need it to be an extra column, but only for the Costomer, which is also unique...

  • but if the data is different, you have to display one per row... and you can’t have more lines in one column... you can even aggregate all the lines in one cell... but you have to know if this is what you need

  • Apparently you expected your BUSINESSDATA_1SAT.CUSTOMER table to have a single record for each value of V_CUSTOMER.CUSTOMER_ID, which does not seem to be the case.

  • I edited the question, I do not understand why the repetition with different values. In question I improved.

  • I don’t think this output matches the query posted since there is a filter in the query where cus.id = 10 and in the output shown there are multiple values of cus.id as customer_id, including 10 is not one of them.

Show 1 more comment

2 answers

1


The problem lies in this line of code:

 V_CUSTOMER.CUSTOMER_ID IN (Select
            cus.id as customer_id
        From
            BUSINESSDATA_1SAT.CUSTOMER cus
        WHERE
            V_CUSTOMER.CUSTOMER_ID = cus.ID)

At no time before in the query are you crossing the tables, only in this SUBSELECT, in case I would rewrite the query more clearly using the INNER JOIN, but answering your question in case you’re bringing all the Cpf and crossing with everyone that exists (IN) in that SUBLECT.

Crossing the data at the most recommended place on INNER JOIN, you avoid headaches, follow the appointment adjusted:

Select
         V_CUSTOMER.CUSTOMER_FIRST_NAME,
         V_CUSTOMER.CUSTOMER_PHONE_2,
         V_CUSTOMER.CUSTOMER_SURNAME,
         V_CUSTOMER.CUSTOMER_TYPE,
         V_CUSTOMER.CUSTOMER_STATUS_ID,
         V_CUSTOMER.CUSTOMER_ID,
         businessdata_1sat.pkg_enc.decrypt_text(businessdata_1sat.customer.reference_number_enc, businessdata_1sat.customer.reference_number_iv) ref_num_CPF_CNPJ
     From
         V_CUSTOMER        
     -------------------------------------------
     --FORMA RECOMENDADA DE CRUZAR TABELAS
     INNER JOIN businessdata_1sat.customer cus
       ON V_CUSTOMER.CUSTOMER_ID = cus.ID
     -------------------------------------------  
     Where
       V_CUSTOMER.CUSTOMER_TYPE <> 'Técnico' And
         V_CUSTOMER.CUSTOMER_STATUS In ('Ativo', 'Pendente') And
         V_CUSTOMER.CUSTOMER_PHONE_2 Is Not Null

  • worked. Thank you very much.

0

Try to use Join

Select * 
from V_CUSTOMER,
join businessdata_1sat.customer on V_CUSTOMER.CUSTOMER_ID = 
     businessdata_1sat.customer.cus.id
Where
     V_CUSTOMER.CUSTOMER_TYPE <> 'Técnico' And
     V_CUSTOMER.CUSTOMER_STATUS In ('Ativo', 'Pendente') And
     V_CUSTOMER.CUSTOMER_PHONE_2 Is Not Null and

Browser other questions tagged mysql sql query oracle

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