What this query does:
Gets multiple users with the first
union; those are the active users.
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
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 bywith the
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