title: sql query - how to join/union 3 tables (2 index tables and 1 data table)
body: <p>i got 3 tables, 2 index tables and 1 data table</p>
  
  <p>tables' structure is like this:</p>
  
  <p><code>users</code>:</p>
  
  <pre><code>id      | name
  --------+----------- 
  1       | John
  2       | Mike
  3       | James
  </code></pre>
  
  <p><code>idx</code> and <code>idx2</code>:</p>
  
  <pre><code>user_id  | user_id2 | action   | date
  ---------+----------+----------+----------
  1        | 2        | message  | 2013-03-20
  1        | 3        | message  | 2013-03-21
  3        | 2        | chat     | 2013-03-21
  </code></pre>
  
  <p>earlier i had just 2 tables, 1 index table (named 'idx') and 1 data table (named 'users'), so the query was something like this:</p>
  
  <pre><code>SELECT * FROM idx inner join users WHERE idx.user_id='2' and idx.user_id2=users.id ORDER BY idx.date DESC
  </code></pre>
  
  <p>but now a new index table (named 'idx2') was added, the structure is similar to 'idx' (i can't combine 'idx' and 'idx2' into a single table, so we need to work with 3 tables at once)</p>
  
  <p>so now i can't make a query with 3 tables</p>
  
  <p>i tried to do something like this:</p>
  
  <pre><code>SELECT * FROM idx,idx2 inner join users WHERE (idx.user_id='2' and idx.user_id2=users.id) or (idx2.user_id='2' and idx2.user_id2=users.id) ORDER BY idx.date DESC
  </code></pre>
  
  <p>but this doesn't work, i guess the query should consist both <code>union</code> and <code>join</code>...</p>
  
  <p>any ideas?</p>
  
  <p>thanks!</p>