Julien S Julien S - 5 months ago 10
SQL Question

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

Here is a simple question, when I have (A,B) as a result of my query, I would like to have only (A,B), not (B,A).

For exemple my query returns :

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


Here is my query :

SELECT S1.SURVEY_ID, S2.SURVEY_ID

FROM SURVEYS S1, SURVEYS S2

WHERE (S2.START_DATE BETWEEN S1.START_DATE and S1.END_DATE
OR S2.END_DATE BETWEEN S1.START_DATE and S1.END_DATE)
AND S1.SURVEY_ID != S2.SURVEY_ID

ORDER BY S1.SURVEY_ID, S2.SURVEY_ID

Answer

Here is one method:

WITH SS as (
      SELECT S1.SURVEY_ID as SURVEY_ID1, S2.SURVEY_ID as SURVEY_ID2
      FROM SURVEYS S1 JOIN
           SURVEYS S2
           ON (S2.START_DATE BETWEEN S1.START_DATE and S1.END_DATE OR
               S2.END_DATE BETWEEN S1.START_DATE and S1.END_DATE
              ) AND
              S1.SURVEY_ID <> S2.SURVEY_ID
     )
SELECT ss.*
FROM ss
WHERE SURVEY_ID1 < SURVEY_ID2
UNION ALL
SELECT ss.*
FROM ss
WHERE SURVEY_ID1 > SURVEY_ID2 AND
      NOT EXISTS (SELECT 1 FROM ss ss2 WHERE ss2.SURVEY_ID1 = ss.SURVEY_ID2 AND ss2.SURVEY_ID2 = ss.SURVEY_ID1);

However, if you just want overlapping surveys, then this would be the appropriate query:

      SELECT S1.SURVEY_ID as SURVEY_ID1, S2.SURVEY_ID as SURVEY_ID2
      FROM SURVEYS S1 JOIN
           SURVEYS S2
           ON S2.START_DATE <= S1.END_DATE AND
              S2.END_DATE >= S1.START_DATE 
              S1.SURVEY_ID < S2.SURVEY_ID;