title: How do I get unique records and ordering on outer join with 3 tables?
body: <p>I have a problem with getting some ordering of records and uniqueness. I have the following setup of tables: </p>
  
  <pre><code>person
  groups
  group_has_person
  </code></pre>
  
  <p>The idea here is to sort persons into groups. (think of Google+ circles)</p>
  
  <p>First I had only a person table with 2 ID fields linking to the group records but then I was always limited to having a person only in 2 groups. Now I have added a linking table so the person can be placed into an unlimited amount of groups. Every group has a sort index field (<code>group.order_index</code>). This is an integer defining the sort-order of the groups. The idea is to show all persons grouped by group and showing every person only once but also order not by name or id but but by the extra index field of the group. So actually I want to show all persons even when they're not in a group. But when they are in one or more groups I want to show them in the order of the <code>group.order_index</code> and only once. </p>
  
  <p>The query result should look like this:</p>
  
  <pre><code>person.name   person.person_id   group.group_id   group.order_index 
  Rick          1                  1                1
  Tom           2                  1                1
  Jan           4                  3                2
  Kees          3                  3                2
  Piet          5                  NULL             NULL
  </code></pre>
  
  <p>Notice that the <code>group_id</code> is not the column to order by but that table has an extra field so the order can be changed after creation of a group. </p>
  
  <p>I got some queries that got close to my expected result but I'm not there yet:</p>
  
  <p>This following query gives the expected sort result but it still gives a person multiple times when it is in multiple groups:</p>
  
  <pre><code>SELECT
    person.person_id AS 'person.person_id',
    person.name AS 'person.name',
    group.order_index AS 'group.order_index',
  FROM
    `person`
  LEFT OUTER JOIN `group_has_person` ON person.person_id = group_has_person.person_id
  LEFT OUTER JOIN `group` ON group_has_person.group_id = group.group_id
  ORDER BY
    3 ASC , 
    2 ASC
  </code></pre>
  
  <p>The key-word <code>DISTINCT</code> didn't help either.</p>
  
  <p>The following query gives unique persons but the <code>order by group_order_index</code> doesn't work and even shows faulty numbers:</p>
  
  <pre><code>SELECT
    person.person_id AS 'person.person_id',
    person.name AS 'person.name',
    group.order_index AS 'group.order_index',
  FROM
    `person`
  LEFT OUTER JOIN `group_has_person` ON person.person_id = group_has_person.person_id
  LEFT OUTER JOIN `group` ON group_has_person.group_id = group.workgroup_id
  GROUP BY
    group_has_person.person_id
  ORDER BY
    3 ASC , 
    2 ASC
  </code></pre>
  
  <p>Also grouping by <code>person.person_id</code> doesn't help getting the right order. </p>
  
  <p>Inner joins also don't work because they wont show persons that are not in a group. Is it possible to get what I want without code and only by a query without having sub-selects?</p>