title: php mysql join multiple table
body: <p>I have two table</p>
  
  <p>User</p>
  
  <pre><code>userID username     email
    1       abc    abc@abc.com
    2       def    def@def.com
    3       ghi    ghi@ghi.com
  </code></pre>
  
  <p>Referral</p>
  
  <pre><code> refID userID      invEmail
     1      1      abc@def.com
     2      1      omg@mog.com
     3      1      def@def.com
  </code></pre>
  
  <p>So what i plan is to give a <code>invited user 5 point</code> meanwhile a <code>inviter 10 point</code>, so basically <code>userID 1</code> gained 30 meanwhile <code>userID 2</code> gained 5. The point i can do in PHP but one part i faced difficulty is when a invEmail to be identified. I don't mind separating into multiple queries if its will work.</p>
  
  <p>How do i show this in sql?</p>
  
  <p>I tried something like</p>
  
  <pre><code>SELECT *, count(r.userID) FROM user u, referral r WHERE u.userID = r.userID OR u.email = r.invEmail GROUP BY r.userID
  </code></pre>
  
  <p>It returned wrong value.</p>
  
  <p><strong>What i would like it to return</strong>, how much count is there inviter and invitee(matched email who has registered based on inviter invitation)</p>
  
  <p>How should i do it?</p>
  
  <p>Thank you.</p>
  
  <p>Edit: i forgot to add something into question, what if i wanted the inviter to receive 10 points only if invitee registered? what i meant is that, only if invEmail exists in u.email then only userID received 10 point. Sorry for my mistake.</p>