Query of similar records

Asked

Viewed 122 times

3

I have a table where I cross exchange data of some professionals, For example, prof A has in common the exchange with prof F, so F has with A as well. But in the permutation report I would not like to show F with A, only A with F, because the information itself is the same thing in practice, although in the bank are different records.

How do I make an SQL that meets this my condition?

For example, Fábio works at UBS Center and wants to move to UBS Anchieta, Anderson works at UBS Anchieta and wants to move to UBS Center. The two register in the Exchange System, in the bank there are the two recorded records, name / origin / option Fábio UBS Centro UBS Anchieta Anderson UBS Anchieta UBS Center

In the report appears: Fabio wants to exchange with Anderson Anderson wants to exchange with Fabio

This duplicity in the report is not necessary.

tabela

mysql sql

  • If they’re the same thing, there should be separate records?

  • Woss, it got a little confusing, but I updated the question. Thank you man.

2 answers

2


I don’t understand why either, as Woss said.

But in any case, an example of how to do:

(there may be other ways, even simpler)

Considering

  • Table = tab
  • PK = id
  • Column 1 = C1
  • Column2 = C2
 select *
 from tab
 where id in (
 select
 (case when (t1.id < t2.id or t2.id is null) then t1.id else null end) c
 from tab t1
 left join tab t2 on t2.c1 = t1.c2 and t2.c2 = t1.c1
 )
 group by c1, c2
 order by id

Example in Fiddle

  • RBZ, thank you for the strength, I think this is the way, but take a look at http://www.sqlfiddle.com/#! 9/3f6926/1, first row A-B and last row B-A, in which case B-A, A-B and B-A would come out in this specific case are the same thing...

  • 1

    @Fábiomartins I think you got a link wrong. I updated the answer later. See again.

  • Note that the select that is in the answer, is no longer the one of the link that passed above, but: http://www.sqlfiddle.com/#! 9/4df86/1

  • 3

    thanks man, it worked. God bless.

  • in the case of 3 columns, A-B-C, C-B-A, it is also possible to do?

  • 1

    Yes... would increase 1 join and various other conditions case.

Show 1 more comment

1

For two columns,

(select q.A, q.B, q.id_da_materia_ou_algo_em_comum_entre_A_e_B
 from
    (select A, B, id_da_materia_ou_algo_em_comum_entre_A_e_B
     from tabela

     intersect

     select B, A, id_da_materia_ou_algo_em_comum_entre_A_e_B
     from tabela) q
where q.A < q.B)

union

(select A, B, id_da_materia_ou_algo_em_comum_entre_A_e_B
from tabela

except

select B, A, id_da_materia_ou_algo_em_comum_entre_A_e_B
from tabela)

For more than two columns, the amount of Intersects/excepts is equal to the factorial of the number of columns,

(select q.A, q.B, q.C, q.emcomum
from 
    (select A, B, C, emcomum
     from tabela
     intersect
     select A, C, B, emcomum
     from tabela
     intersect
     select C, A, B, emcomum
     from tabela
     intersect
     select C, B, A, emcomum
     from tabela
     intersect
     select B, C, A, emcomum
     from tabela
     intersect
     select B, A, C, emcomum
     from tabela) q
where q.A < q.B and q.B < q.C)

union

(select A, B, C, emcomum
 from tabela
 except
 select B, A, C, emcomum
 from tabela
 except
 select B, C, A, emcomum
 from tabela
 except
 select C, B, A, emcomum
 from tabela
 except
 select C, A, B, emcomum
 from tabela
 except
 select A, C, B, emcomum
 from tabela)
  • 1

    worked with your answer too. Thank you :)

Browser other questions tagged mysql sql

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