title: SQL query with count and left join
body: <p>I have a query, where i need to calculate 'userID' from 2 tables.</p>
  
  <p>SQL QUERY:</p>
  
  <pre><code>    SELECT DISTINCT TOP 1000 u.id as userID, u.firstName as userFirstName, u.email as userEmail, u.phone as userPhone, 
  count(ueo.userID) as opensEmailCounter, count(ush.userID) as opensSmsCounter
      FROM dbo.Users u
      LEFT JOIN dbo.UserEmailsOpens ueo ON u.id = ueo.userID AND ueo.targetID = 4
      LEFT JOIN dbo.UserSmsHistory ush ON u.id = ush.userID AND ush.targetID = 4 AND ush.opened = 1
      WHERE u.deleted = 0
      AND IsNull(u.firstName, '') != '' 
      AND IsNull(u.email, '') != '' 
      AND IsNull(u.phone, '') != ''
  GROUP BY u.id, u.firstName, u.email, u.phone
  </code></pre>
  
  <p>But, the result is not what i expected. It giving to me the wrong numbers after i did the second left join. It twice my result in some cases and showing the same count result (attached the screenshot).
  <a href="https://i.stack.imgur.com/INrv1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/INrv1.png" alt="enter image description here"></a></p>