title: How to get fields from a table where its primary key is used in another table in 2 possible fields
body: <p>I have 2 sql tables</p>
  
  <pre><code>    CREATE TABLE users(userID INTEGER PRIMARY KEY AUTOINCREMENT, alias TEXT NOT NULL)
      CREATE TABLE friends(userID1 INTEGER, userID2 INTEGER, PRIMARY KEY(userID1, userID2))
  </code></pre>
  
  <p>When 2 users want to be friends, I create a new relationship btw the 2 by inserting a row in the <strong>friends</strong> table. The order for the <strong>userID</strong> of the 2 friends in the row do not matter but it should be unique, whatever the order.</p>
  
  <p><strong>Question 1:</strong>
  My second table do not currently reflect what I want. I mean I can do this and it's fine while I don't want it to be:</p>
  
  <pre><code>    INSERT INTO friends VALUES(1,2);
      INSERT INTO friends VALUES(2,1);
  </code></pre>
  
  <p>Is it possible to enforce this constraint in my sql table, or should I check with a query if there is not already a relationship in the reversed order.</p>
  
  <p><strong>Question 2:</strong>
  What is the most optimal sql query to get the list of friends (<strong>userID</strong> and <strong>alias</strong>) if I have a <strong>userID</strong>, if I have for example:</p>
  
  <pre><code>    INSERT INTO users VALUES(1, 'A');
      INSERT INTO users VALUES(2, 'B');
      INSERT INTO users VALUES(3, 'C');
      INSERT INTO users VALUES(4, 'D');
      INSERT INTO friends VALUES(1, 2);
      INSERT INTO friends VALUES(2, 3);
      INSERT INTO friends VALUES(4, 1);
      INSERT INTO friends VALUES(2, 4);
  </code></pre>
  
  <p>I want:</p>
  
  <pre><code>For userID=1: (2, B) and (4, D)
  For userID=2: (1, A), (3, C) and (4, D)
  For userID=3: (2, B)
  For userID=4: (1, A) and (2, B)
  </code></pre>
  
  <p><strong>Bonus question:</strong> Can I improve the tables to better reflect what I want?</p>
  
  <p>Thanks a lot!</p>