CODEWITHSUNDEEP
sqlsql-server-2008-r2case
shilezi
shilezi

Reputation: 13

select from multiple tables, same database case conditional

I have a question similar to this, 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).

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

+----------+-------+-------+
|emailAddy | site1 | site2 |
+------+-----------+-------+
|[email protected] | 0     | 1     |
|[email protected] | 1     | 0     |
|[email protected] | 1     | 1     |
|[email protected] | 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

Any help? Thanks.

Upvotes: 0

Views: 7944

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146559

Select u.emailAddress,
   case when exists(Select * from Users_Sites 
                    Where SiteId = 1 
                      And UserId = u.UserId) 
        then 'x' End Site1,
   case when exists(Select * from Users_Sites 
                    Where SiteId = 2 
                      And UserId = u.UserId) 
        then 'x' End Site2
From users u

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

First, get rid of the implicit JOIN (replace it with an explicit JOIN as shown below), as it is you've got no criteria and are just joining each record to every record in the other table.

Then just wrap your conditionals in an aggregate:

SELECT emailaddress
      ,MAX(CASE WHEN cast(siteid AS varchar(max))= '1' THEN 1 ELSE 0 END) AS site1
      ,MAX(CASE WHEN cast(siteid AS varchar(max))= '2' THEN 1 ELSE 0 END) AS site2
FROM Users u
JOIN Users_Sites us
   ON u.user_id = us.user_id  --No idea what your actual join criteria should be.
GROUP BY emailaddress

Also don't need DISTINCT when using GROUP BY, and siteid shouldn't be in your GROUP BY if you don't want one record returned per siteid.

Edit, didn't even see your implicit JOIN

Upvotes: 1

Related Questions