Query of non-similar records

Navigation
2

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

How do I make a SQL that meets this condition?

Example, Fábio works at UBS Centro and wants to move to UBS Anchieta, Anderson works at UBS Anchieta and wants to move to UBS Centro. The two are registered in the Swap System, in the bank there are the two records recorded, name / source / option Fábio UBS Center UBS Anchieta Anderson UBS Anchieta UBS Centro

The report displays: Fábio wants to barter with Anderson Anderson wants to exchange with Fabio

This duplicity in the report is not required.

    
asked by anonymous 25.06.2018 / 17:31

2 answers

2

I also did not understand why Woss said.

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

(there may be other ways, even simpler)

Considering

  • Table = tab
  • PK = id
  • Column1 = 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 on Fiddle

    
25.06.2018 / 18:49
1
(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 three columns, A, B, and C, you should place the column permutations as shown below, in the queries on each side of UNION, 

select A, B, C ...
except/intersect
select A, C, B ...
except/intersect
select B, A, C ...
except/intersect
select B, C, A ...
except/intersect
select C, A, B ...
except/intersect
select C, B, A

The amount of SELECTs on each side of UNION will be equal to the permutation of the number of columns. In mathematical terms, n! .

The WHERE clause on the left side of UNION should be: 

WHERE q.A < q.B AND q.B < q.C ... (AND q.C < q.D AND q.D < q.E) ....
    
26.06.2018 / 19:51
Can anyone tell me what is causing this error How to change this PHP 5.3 code to PHP 5.4?