sql - SELECT only one pair if I have both (A, B) and (B, A) -

here simple question, when have (a,b) result of query, have (a,b), not (b,a).

for exemple query returns :

161, 52  161, 53 53, 161 53, 161 

here query :

select s1.survey_id, s2.survey_id    surveys s1, surveys s2    (s2.start_date between s1.start_date , s1.end_date   or s2.end_date between s1.start_date , s1.end_date)   , s1.survey_id != s2.survey_id  order s1.survey_id, s2.survey_id 

here 1 method:

with ss (       select s1.survey_id survey_id1, s2.survey_id survey_id2       surveys s1 join            surveys s2            on (s2.start_date between s1.start_date , s1.end_date or                s2.end_date between s1.start_date , s1.end_date               ) ,               s1.survey_id <> s2.survey_id      ) select ss.* ss survey_id1 < survey_id2 union select ss.* ss survey_id1 > survey_id2 ,       not exists (select 1 ss ss2 ss2.survey_id1 = ss.survey_id2 , ss2.survey_id2 = ss.survey_id1); 

however, if want overlapping surveys, appropriate query:

      select s1.survey_id survey_id1, s2.survey_id survey_id2       surveys s1 join            surveys s2            on s2.start_date <= s1.end_date ,               s2.end_date >= s1.start_date                s1.survey_id < s2.survey_id;