title: select from multiple tables, same database case conditional
body: <p>I have a question similar to <a href="https://stackoverflow.com/questions/16397666/split-column-values-into-multiple-columns-with-create-view">this</a>, but with a little bit more twist and pertaining to sql server 2k8r2.
  Database has 3 tables. sites, users, users_sites. I need to get a list of emailaddresses from users table plus 2 columns indicating what sites they're a member. users could be either a member of 1, 2 or none(most likely inactive).</p>
  
  <p>here is my script but i keep getting a 2 rows for each email but my aim is to get 3 columns of email, site1 member as 1 or site2 member as 0 or if members of both, 1,1 and if none 0,0</p>
  
  <pre><code>+----------+-------+-------+
  |emailAddy | site1 | site2 |
  +------+-----------+-------+
  |1@test.co | 0     | 1     |
  |1@test.co | 1     | 0     |
  |2@test.jp | 1     | 1     |
  |2@test.jp | 1     | 1     |
  +----------+-------+-------+
  
  
  SELECT distinct emailaddress
  ,CASE WHEN cast(siteid AS varchar(max))= '1' THEN 1 ELSE 0 END AS site1
  ,CASE WHEN cast(siteid AS varchar(max))= '2' THEN 1 ELSE 0 END AS site2
  FROM Users, Users_Sites 
  GROUP BY emailaddress,siteID
  </code></pre>
  
  <p>Any help?
  Thanks.</p>