title: Select count(*) and "join" over 3 tables
body: <p>I am looking for a count query within a join of 3 tables that can get me counts on distinct values of one of these tables.</p>
  
  <p>I have 3 tables that I need to join to get the expected data (<code>Workflow</code>, <code>Message</code> and <code>Message_Workflow_Relation</code>).</p>
  
  <p>I want to get the count of workflows grouped by status + one field of the connected <code>Message</code> table in my result (<code>related_name</code>). The related name should be taken from entries where the <code>adapter</code> field equals <code>wf</code>, however there are sometimes more than one Message record that matches this condition, which will result in more datasets in my count then the ones that are really there.</p>
  
  <p>I am pretty sure it must be possible to sort out the , but just don't get it working.
  Sadly I cannot change the table structure as it's a given schema of a product we use.</p>
  
  <p>My table structure looks like this:</p>
  
  <p><b>Workflow:</b></p>
  
  <pre><code>id | workflow_id | starttime | endtime | status
  ------------------------------------------------------
  1  |          22 |         0 |     200 |     OK
  2  |          23 |       220 |     920 |  ERROR
  3  |          55 |       202 |     588 |     OK
  </code></pre>
  
  <p><b>Message_Workflow_Relation:</b></p>
  
  <pre><code>id | message_id | workflow_id |
  -------------------------------
  1  |        122 |          22 |
  2  |        235 |          22 |
  3  |        456 |          22 |
  4  |        982 |          22 |
  5  |        444 |          23 |
  6  |        445 |          23 |
  7  |        585 |          55 |
  8  |        738 |          55 |
  9  |        399 |          55 |
  </code></pre>
  
  <p><b>Message:</b></p>
  
  <pre><code>id | message_id | starttime | endtime | adapter | related_name |
  ----------------------------------------------------------------
   1 |        122 |         0 |    2335 |      wf |   workflow_1 |
   2 |        235 |       222 |    1000 |   other |        other |
   3 |        456 |       343 |    2330 | another |      another |
   4 |        982 |       222 |    2200 |      wf |   workflow_1 |
   5 |        444 |      2223 |    3333 |      wf |   workflow_2 |
   6 |        445 |      1123 |    1244 |  manual |       manual |
   7 |        585 |      5555 |    5566 |      wf |   workflow_1 |
   8 |        738 |       655 |     999 |      wf |   worfklow_1 |
   9 |        399 |      6655 |    7732 | another |      another |
  </code></pre>
  
  <p>This should return the following result:</p>
  
  <pre><code>count(*) | related_name | status |
  ----------------------------------
         2 |   workflow_1 |     OK |
         1 |   workflow_2 |  ERROR |
  </code></pre>
  
  <p>I am stuck with this following statement, but I am not sure how to 
  make the selection on the <code>adapter = wf unique</code> for each workflow:</p>
  
  <pre><code>select distinct
    count(*),
    m.related_name,
    w.status
  from
    workflow as w,
    message as m,
    msg_bpm_rel as rel
  where rel.workflow_id = w.workflow_id
    and rel.message_id = m.message_id
    and m.adapter = 'PE'
  group by m.related_name,w.status
  </code></pre>
  
  <p>This returns me (4 <code>workflow_1</code> instead of 2):</p>
  
  <pre><code>count(*) | related_name | status |
  ----------------------------------
         4 |   workflow_1 |     OK |
         1 |   workflow_2 |  ERROR |
  </code></pre>
  
  <p>How can I make a correct query to achieve this?</p>
  
  <p>Any help appreciated.</p>