title: SQL: how to compare data in different rows and only select unique "pairs" assuming there are only two colums?
body: <p>I have several tables (<strong>bold</strong> means primary key):</p>
  <blockquote>
  <p>Dancer(<strong>dancer_name,</strong> gender, age)</p>
  <p>Dance(<strong>dancer_name, dvd_id,</strong> song_title)</p>
  <p>Dvd(<strong>dvd_id,</strong> song_title, cost)</p>
  <p>Song(<strong>dancer_name, song_title,</strong> genre)</p>
  <p>Launch(<strong>dancer_name, dvd_id,</strong> year)</p>
  </blockquote>
  <p>I want to select the pairs of dancers whose song appear together in one or more dvds and for each pair to only print out once.</p>
  <p>This is as close as I could get and it prints out the same pair twice, but their names in different columns:</p>
  <pre><code>select distinct DANCER1.dancer_name, DANCER2.dancer_name, count(*) as count
    from Dancer DANCER1, Dancer DANCER2, Dance DANCE1, Dance DANCE2
   where DANCER1.dancer_name = DANCE1.dancer_name 
     and DANCER2.dancer_name = DANCE2.dancer_name 
     and DANCER1.dancer_name &lt;&gt; DANCER2.dancer_name
     and DANCE1.dvd_id = DANCE2.dvd_id
   group by DANCER1.dancer_name, DANCER2.dancer_name;
  </code></pre>
  <p><strong>So instead of getting</strong></p>
  <pre><code>Tom Jon
  Jon Tom
  Bob Sam
  Sam Bob
  </code></pre>
  <p><strong>I just want</strong></p>
  <pre><code>Tom Jon
  Bob Sam
  </code></pre>