title: MySQL query with calculation from two tables
body: <p>I'm using MySQL 4.2.</p>
  
  <p>I have 2 tables:</p>
  
  <ul>
  <li><code>tbl_User</code> stores USERID and REGCODE. REGCODE is the code user used for signup the service.</li>
  <li><code>tbl_Message</code> stores messages posted by each of those users. </li>
  </ul>
  
  <p>I need to query all users in tbl_User for the corresponding REGCODE to get total.
  Then, I would like to know is how many of these users have at least one entry in tbl_Message where their USERID matches up with AUTHORID.
  The last row is the % of user has post.</p>
  
  <p>My goal is to calculate the percentage from two tables at last row, but I am not sure how to join tables in query to get the correct answer. Thanks for your help!</p>
  
  <p>Tables chart:
  <a href="http://img526.imageshack.us/img526/6105/tablep.png" rel="nofollow">http://img526.imageshack.us/img526/6105/tablep.png</a></p>
  
  <p>Here is the query I am using:</p>
  
  <pre><code>Select 'Percentage',
    Sum(Case
      When tbl_User.REGCODE = 9001 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9001 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9002 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9002 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9003 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9003 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9004 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9004 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9005 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9005 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9006 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9006 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9007 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9007 Then 1 Else 0 End) * 100.0,
    Sum(Case
      When tbl_User.REGCODE = 9008 And
     tbl_User.USERID = tbl_Message.AUTHORID Then 1 Else 0
    End) / Sum(Case When tbl_User.REGCODE = 9008 Then 1 Else 0 End) * 100.0
  From tbl_User 
  left Join tbl_Message
  tbl_Message ON tbl_User.USERID = tbl_Message.AUTHORID 
  Where tbl_Message.AUTHORID IS NOT NULL
  </code></pre>