Compare data from the same table.

[nollries]

Hello friends,

I'm new here in the forum and also in SQL ...

Well, I need to make the following comparison. I have a product table and need to compare whether the product has the same information filled as well as in the B product and what are they, something like that.

Thanks from now

valeu!

[JLM]

At first we can consider the following research:

Select all

 SELECT * FROM PRODUTO T1, PRODUTO T2 
            WHERE T1.CODPRO <> T2.CODPRO 
                AND (T1.COLUNA1 = T2.COLUNA1   OR 
                         T1.COLUNA2 = T2.COLUNA2   OR 
                         T1.COLUNA3 = T2.COLUNA3   OR 
                         ..... ) 

: Wink:

[nollries]

Speak jml beauty?

First thanks for the attention!

... I will try to apply this solution that v. indicated and return with the results ...

valeu!

: arrow:

[fsitja]

The definition of what you need is half vague ... Suddenly with something more punctual give to suggest something more specific to your need.

[nollries]

Good morning FSITJA ,

really,

good then is the following.

I have a product table (medicines) where the medicine commercial has been informed in its registration its correspondent generic which is also registered in the same table and has its corresponding commercial informed. Based on, in some situations I call the trade commercial but use its generic , and vice versa, for stock movement, collection etc. In some cases the two registrations should be exactly equal no more, so I need to compare the medicines ( commercial x generic ) in order to identify differences in the registrations and preferably which fields are informed in one and the other is not / or what information has in the field to of the commercial and what has in the field a of the generic field.

... I thank the information of the friend jml , but I have not yet been able to adapt to what I need.

Good, I believe that with this information above is possible to better understand the situation.

Thank you for the strength !!!

[fsitja]

NOLLRIES, I made two examples of SELECT with different forms of displaying this type:

Select all

 
SQL> create table produto (cod_produto       number(10) primary key, 
  2                        nome_produto      varchar2(100) not null, 
  3                        preço             number(15, 2), 
  4                        tipo_receita      varchar2(30) check (tipo_receita in ('A', 
  5                                                                               'B', 
  6                                                                               'ESPECIAL', 
  7                                                                               'TALIDOMIDA', 
  8                                                                               'CONTROLE ESPECIAL')), 
  9                        cod_med_comercial number(10) references produto (cod_produto)); 
  
Table created 
SQL> begin 
  2  insert into produto values (1, 'PROD_X', 20.99, 'ESPECIAL', null); 
  3  insert into produto values (2, 'PROD_X1', 15.99, 'ESPECIAL', 1); 
  4  insert into produto values (3, 'PROD_X2', 13.99, 'A', 1); 
  5  insert into produto values (4, 'PROD_N', 9.99, null, null); 
  6  insert into produto values (5, 'PROD_N1', 7.99, null, 4); 
  7  commit; 
  8  end; 
  9  / 
  
PL/SQL procedure successfully completed 
SQL> SELECT p.cod_produto  cod_comercial, 
  2         g.cod_produto  cod_generico, 
  3         p.nome_produto nome_comercial, 
  4         g.nome_produto nome_generico, 
  5         p.preço        preço_comercial, 
  6         g.preço        preço_generico, 
  7         p.tipo_receita receita_comercial, 
  8         g.tipo_receita receita_generico 
  9    FROM produto p 
 10    JOIN produto g 
 11      ON g.cod_med_comercial = p.cod_produto 
 12   WHERE p.tipo_receita != g.tipo_receita 
 13         OR p.preço < g.preço; 
  
COD_COMERCIAL COD_GENERICO NOME_COMERCIAL NOME_GENERICO   preço_COMERCIAL    preço_GENERICO RECEITA_COMERCIAL RECEITA_GENERICO 
------------- ------------ -------------- ------------- ----------------- ----------------- ----------------- ---------------- 
            1            3 PROD_X         PROD_X2                   20,99             13,99 ESPECIAL          A 
SQL> SELECT p.cod_produto, 
  2         p.nome_produto, 
  3         PRIOR p.nome_produto nome_com, 
  4         p.preço, 
  5         PRIOR p.preço preço_com, 
  6         p.tipo_receita, 
  7         PRIOR p.tipo_receita rec_com 
  8    FROM produto p 
  9  START WITH p.cod_med_comercial IS NULL 
 10  CONNECT BY PRIOR p.cod_produto = p.cod_med_comercial; 
  
COD_PRODUTO NOME_PRODUTO NOME_COM             preço  preço_COM TIPO_RECEITA REC_COM 
----------- ------------ -------- ----------------- ---------- ------------ --------- 
          1 PROD_X                            20,99            ESPECIAL      
          2 PROD_X1      PROD_X               15,99      20,99 ESPECIAL     ESPECIAL 
          3 PROD_X2      PROD_X               13,99      20,99 A            ESPECIAL 
          4 PROD_N                             9,99                          
          5 PROD_N1      PROD_N                7,99       9,99               
  
SQL>  

Do not give ball to my sample table ... It's silly, just for testing.

The first Select makes a self-join to list all generic drugs next to his commercial equivalent. Then I put a filter on the WHERE to select those who had a difference of the revenue type attribute and whose price of the generic medicine was more expensive than the commercial (!): LOL:

A Connect by (SELECT hierarchical) to pick up all medicines and below them list their generics, comparing the attributes. You can encapsulate this SELECT on a Subquery (or View) to filter discrepancies and other things.

is a beginning, if you have suggestions or corrections to point, feel at ease.

[nollries]

Good morning,

Thanks FSITJA For the code, I'll take a look and see what I get. Probably only respond at the end of the week - the last few days are punk: -The in the facul and at work ... - More for what I analyzed for sure will help a lot.

Return as soon as possible, thanks !!!

See more.