title: SQL - Avoiding duplicate pairs on a self join/inner join?
body: <p>I have database where among other tables I have this one:</p>
  
  <pre><code>CREATE TABLE `Physiotherapist`(
  `pid` INT,
  `name` VARCHAR(40),
  `hours` INT,
  `cid` INT    
  </code></pre>
  
  <p>I want to write an SQL query that returns a list of pairs of physiotherapists that work at the same clinic but will
  not return duplicates. My answer so far is: </p>
  
  <pre><code>SELECT  p1.name AS name1, p2.name AS name2
  FROM    Physiotherapist p1 
  INNER JOIN Physiotherapist p2 on p2.cid = p1.cid AND p1.name != p2.name
  </code></pre>
  
  <p>or alternate solution with self join: </p>
  
  <pre><code>SELECT  p1.name, p2.name
  FROM    Physiotherapist p1, Physiotherapist p2
  WHERE   p1.cid = p2.cid AND p1.name !=(SELECT p2.name)
  </code></pre>
  
  <p>Either way I get this result:</p>
  
  <pre><code>name1:            name2:
  
  Jan Christensen   Ira Assent
  Ira Assent        Jan Christensen
  </code></pre>
  
  <p>I.e. the same pair. I only want one of the duplicates, as they display the same information, but I cannot figure out a convenient way to do it. </p>
  
  <p>EDIT: The full database code with sample data</p>
  
  <pre><code>DROP TABLE IF EXISTS Physiotherapist;
  
  CREATE TABLE `Physiotherapist`(
      `pid` INT,
      `name` VARCHAR(40),
      `hours` INT,
      `cid` INT
  );
  
  INSERT INTO Physiotherapist VALUES('123','Ira Assent','8','1');
  INSERT INTO Physiotherapist VALUES('246','Annika Schmidt','1','2');
  INSERT INTO Physiotherapist VALUES('327','Jan Christensen','5','1');
  INSERT INTO Physiotherapist(`pid`,`name`) VALUES('455','Simon Winter');
  
  DROP TABLE IF EXISTS Clinic;
  
  CREATE TABLE `Clinic`(
      `cid` INT,
      `name` VARCHAR(40),
      `address` VARCHAR(40)
  );
  
  INSERT INTO Clinic VALUES ('1','PhysCentral','Aarhus C');
  INSERT INTO Clinic VALUES ('2','PhysIOgnomy','Aarhus N');
  INSERT INTO Clinic VALUES ('3','Physios','Aarhus V');
  
  DROP TABLE IF EXISTS Appointment;
  
  CREATE TABLE `Appointment`(
      `aid` INT,
      `date` DATE,
      `time` INT,
      `pid` INT
  );
  
  INSERT INTO Appointment VALUES(34716,'2018-06-12','10','246');
  INSERT INTO Appointment VALUES(23118,'2018-08-18','11','327');
  INSERT INTO Appointment VALUES(88223,'2018-10-03','9','246');
  </code></pre>