Reputation: 85
hope someone can help. I have two tables:
Users
-UserID
-UserName
UsersType
-UserTypeID
-UserID
Possible values for UsersTypeID is 1 to 6. In that scenario Users may have multiple types and I need to retrieve a distinct row for each user with the columns described below.
UserName - Type1 - Type2 - Type3 - Type4
Joe 0 1 1 0
In this scenario, Joe has two different User type (2,3)
This might be easy as pie but I have been working around this for so long that I am clueless. Can some one help please.
Upvotes: 3
Views: 1919
Reputation: 64674
This is a standard crosstab output which you should be able to google. Although not recommended in SQL you can do something like:
Select Users.Username
, Max( Case When UsersType.UserTypeId = 1 Then 1 Else 0 End ) As Type1
, Max( Case When UsersType.UserTypeId = 2 Then 1 Else 0 End ) As Type2
, Max( Case When UsersType.UserTypeId = 3 Then 1 Else 0 End ) As Type3
, Max( Case When UsersType.UserTypeId = 4 Then 1 Else 0 End ) As Type4
From Users
Join UsersType
On UsersType.UserId = Users.UserId
Group By Users.UserName
(Updated to Max instead of Min)
Upvotes: 4
Reputation: 8261
You want to use a LEFT JOIN. Try something like this
SELECT
UserName
U1.UserTypeID AS Type1
U2.UserTypeID AS Type2
U3.UserTypeID AS Type3
U4.UserTypeID AS Type4
FROM Users U
LEFT JOIN UsersType U1 ON U.UserID = U1.UserID AND UserTypeID = 1
LEFT JOIN UsersType U2 ON U.UserID = U2.UserID AND UserTypeID = 2
LEFT JOIN UsersType U3 ON U.UserID = U3.UserID AND UserTypeID = 3
LEFT JOIN UsersType U4 ON U.UserID = U4.UserID AND UserTypeID = 4
If you want the rows to just return true or false then I would do this(it is SQL Server specific):
SELECT
UserName
CASE WHEN U1.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type1
CASE WHEN U2.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type2
CASE WHEN U3.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type3
CASE WHEN U4.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type4
FROM Users U
LEFT JOIN UsersType U1 ON U.UserID = U1.UserID AND UserTypeID = 1
LEFT JOIN UsersType U2 ON U.UserID = U2.UserID AND UserTypeID = 2
LEFT JOIN UsersType U3 ON U.UserID = U3.UserID AND UserTypeID = 3
LEFT JOIN UsersType U4 ON U.UserID = U4.UserID AND UserTypeID = 4
Upvotes: 0
Reputation: 5105
To get exactly the result set you are looking for the query is ugly and not very scalable (for example, if you had 100 usertypes) but here you go
select
u.username,
isnull(ut1.Usertypeid,0) as Type1,
isnull(ut2.Usertypeid,0) as Type2,
isnull(ut3.Usertypeid,0) as Type3,
isnull(ut4.Usertypeid,0) as Type4,
isnull(ut5.Usertypeid,0) as Type5,
isnull(ut6.Usertypeid,0) as Type6
from
users u
left outer join
userstype ut1 on u.userid = ut1.userid and ut1.usertypeid = 1
left outer join
userstype ut2 on u.userid = ut2.userid and ut2.usertypeid = 2
left outer join
userstype ut3 on u.userid = ut3.userid and ut3.usertypeid = 3
left outer join
userstype ut4 on u.userid = ut4.userid and ut4.usertypeid = 4
left outer join
userstype ut5 on u.userid = ut5.userid and ut5.usertypeid = 5
left outer join
userstype ut6 on u.userid = ut6.userid and ut6.usertypeid = 6
EDIT
Once you've got to your 10th user type hopefully common sense will kick in (hopefully before the 10th) and you'll think to yourself this can't go on!
When that happens you'd be better off querying like this
select
u.username,
ut..Usertypeid
from
users u
left outer join
userstype ut
on u.userid = ut.userid
You would then get a result set that looks like
Username | UserTypeID
---------------------
Joe | 3
Joe | 4
which you'll have to loop through at your client end BUT is a lot kinder to your database server and your sanity!
Upvotes: 0
Reputation: 23613
The known, limited number of UserTypeID values makes this (relatively) straightforward strategy possible. This looks hairy, but as long as your indexes are good the performance should be good.
Select U.UserName, Case When UT1.UserType Is Null Then 0 Else 1 End As Type1,
Case When UT2.UserType Is Null Then 0 Else 1 End As Type2,
Case When UT3.UserType Is Null Then 0 Else 1 End As Type3,
Case When UT4.UserType Is Null Then 0 Else 1 End As Type4,
Case When UT5.UserType Is Null Then 0 Else 1 End As Type5,
Case When UT6.UserType Is Null Then 0 Else 1 End As Type6,
From Users As U
Left Join UsersType As UT1 On U.UserID = UT1.UserID
Left Join UsersType As UT2 On U.UserID = UT2.UserID
Left Join UsersType As UT3 On U.UserID = UT3.UserID
Left Join UsersType As UT4 On U.UserID = UT4.UserID
Left Join UsersType As UT5 On U.UserID = UT5.UserID
Left Join UsersType As UT6 On U.UserID = UT6.UserID
Where UT1.UserTypeID = 1 And UT2.UserTypeID = 2
And UT3.UserTypeID = 3 And UT4.UserTypeID = 4
And UT5.UserTypeID = 5 And UT6.UserTypeID = 6
You would often come up with an alternate strategy if you want the ability to add UserTypes and still have this work. In this case, you'll need a udf or stored procedure to construct the query and run it.
Upvotes: 0
Reputation: 196306
Using pivot you can do
SELECT
UserName,
[1] as 'type1',
[2] as 'type2',
[3] as 'type3',
[4] as 'type4',
[5] as 'type5',
[6] as 'type6'
FROM (
SELECT
UserName,
userTypeId
FROM
users LEFT OUTER JOIN UsersType
ON users.userId = usersType.userid
) AS src
PIVOT (
count(userTypeId) FOR userTypeId IN ([1],[2],[3],[4],[5],[6]) ) AS pvt
Upvotes: 1
Reputation: 10541
Since you're stating there will only be 6 values for the type, I can suggest using subqueries:
SELECT UserName
,(
SELECT CONVERT(bit, COUNT(UserTypeID))
FROM UsersType AS ut
WHERE ut.UserID = u.UserID
AND ut.UserTypeID = 1
) AS Type1
,(
SELECT CONVERT(bit, COUNT(UserTypeID))
FROM UsersType AS ut
WHERE ut.UserID = u.UserID
AND ut.UserTypeID = 2
) AS Type2
,...
FROM Users AS u
Upvotes: 0
Reputation: 24152
SELECT U.[UserName]
, AVG(CASE WHEN UT.[UserTypeID] IS 1 THEN 1 ELSE NULL END) AS N'Type 1'
, AVG(CASE WHEN UT.[UserTypeID] IS 2 THEN 2 ELSE NULL END) AS N'Type 2'
, AVG(CASE WHEN UT.[UserTypeID] IS 3 THEN 3 ELSE NULL END) AS N'Type 3'
, AVG(CASE WHEN UT.[UserTypeID] IS 4 THEN 4 ELSE NULL END) AS N'Type 4'
, AVG(CASE WHEN UT.[UserTypeID] IS 5 THEN 5 ELSE NULL END) AS N'Type 5'
, AVG(CASE WHEN UT.[UserTypeID] IS 6 THEN 6 ELSE NULL END) AS N'Type 6'
FROM [Users] U
INNER JOIN [UserType] UT ON UT.[UserID] = U.[UserID]
GROUP BY U.[UserName]
ORDER BY U.[UserName]
Upvotes: 1