Query for combining two SELECTs to get active users and 1 inactive user

Question

What this query does:

1. Gets multiple users with the first select in the union; those are the active users.

2. But when a user is not in that list, but there is a @userid, it should get that user as well and set active to 0 there; that is what the second select does.

3. If a user is in the first select, and in the second select, the second one should not be in the resultset; that is what the group by with the max is for.

Is there a more efficient way to write this query?

SELECT pk_userid,
       username,
       CAST(MAX(active) AS BIT) AS active
FROM   (SELECT     pk_userid,
                   username,
                   1 AS active
        FROM       dbo.tbluser u
        INNER JOIN tblCustomer c ON u.pk_userid = c.fk_userid
        WHERE      ( c.fk_projectid = @projectid
                 AND c.fk_orderid = @orderid )
        UNION
        SELECT pk_userid,
               username AS username,
               0        AS active
        FROM   dbo.tbluser u
        WHERE  pk_userid = @userid) a
GROUP  BY pk_userid,
          username
ORDER  BY username

Answer 1

Using a CTE will make this query a lot cleaner and probably speed it up a bit as well.

WITH UnionTable AS
(
    SELECT     pk_userid,
               username,
               1 AS active
    FROM       dbo.tbluser AS u
        INNER JOIN tblCustomer AS customer ON u.pk_userid = customer.fk_userid
    WHERE      ( customer.fk_projectid = @projectid
                 AND customer.fk_orderid = @orderid )
    UNION
    SELECT u.pk_userid,
           u.username AS username,
           0        AS active
    FROM   dbo.tbluser AS u
    WHERE  u.pk_userid = @userid
)
SELECT 
        pk_userid
        , username
        , CAST(MAX(active) AS BIT) AS active
FROM UnionTable
GROUP BY pk_userid, username
ORDER BY username

The only thing that concerns me is that you label 2 tables in the first select (nested query) but you don't specify which table you want the pk_userid or username to come from, are they only available in the user table? Even if they only exist in the one table, you should get used to using the aliases you create so that it is clear where the columns are being called from.

You should also make sure that your aliases are meaningful as well. I changed c to customer but I left u as u because user is a reserved word in SQL