title: Select Distinct for multiple joins
body: <p>I am facing a problem here, I need to do a query that is like this following :</p>
  
  <pre><code>  select client.name, client18n.display_name
          FROM Client client          
          JOIN TEAM team on (client.id = team.client_id)
          JOIN TEAM_KGK_USER members on (members.team_members_id = team.id)
          JOIN USER users on (users.id = members.user_id
          JOIN CLIENT_I18N client18n on (client.id = client18n.client_id)
          Where users.id = @userId
  </code></pre>
  
  <p>The main idea of the query is, I need to return all clients given my user id, because the user is part of a multiple teams, and each team has one client that owns this team. but I can not return duplicate clients, so I was trying to use the distinct, but than I have few problems:</p>
  
  <ul>
  <li>One Client can own multiple teams that the user is a member</li>
  <li>Another where clause is that I need to get the locale from the Client_I18N, for a given locale ID that the user can pass into this query, but for example I can pass fr_FR but if does not exist for that client I need to return the display name for en_US as a fall back</li>
  <li>I need to take in account that the query will have max and off set</li>
  <li>I need to order by the clientI18n.display_name</li>
  </ul>
  
  <p>I am using Oracle database, to illustrate my problems here we go:</p>
  
  <p>Imagine user : John is member of team A, team B, team C and team D</p>
  
  <p>so the respective clients of these teams are:</p>
  
  <p>Team A : ClientA
  Team B : ClientA
  Team C : ClientB
  Team D : ClientC</p>
  
  <p>for the clients they have multiple values in the ClientI18N:</p>
  
  <p>ClientA: ClientA_US
  ClientA: ClientA_FR</p>
  
  <p>ClientB: ClientB_US
  ClientB: ClientB_FR</p>
  
  <p>and go on...</p>
  
  <p>so if I look for the user John</p>
  
  <p>Will return twice ClientA for me even if I use distinct, because I will be distinct into client.name and client18n.display_name.</p>
  
  <p>I tried to return than just the client.name as it is a unique value but I need to order By the client18n.display_name, because if i don't the offset won't work.</p>
  
  <p>this is what i get when I run the query for example:</p>
  
  <pre><code>  NAME                  DISPLAY_NAME  
    Client_38276QVAAU3    GTA_38276QVAAU3
    Client_38276QVAAU     GTA_38276QVAAU
    Client_38276QVAAU     el cliente!
    Client_38276QVAAU3    der client!
    Client_38276QVAAU2    le client!
    Client_38276QVAAU2    GTA_38276QVAAU2
  </code></pre>
  
  <p>as you can see even if I use distinct will return duplicate client name</p>