Select the same column multiple times

Question

Hello, 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.

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 4

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!

Answer 5

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]

Answer 6

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

Answer 7

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