How to return list of content from subquery for the main query?

Question

I have a create that have sub query. To show as example, here is a query that has similarity with my own. Personally I have more table and more arguments on table (The subquery have left join for example). I simplified it for the question.

SELECT
   Content,
   CASE 
      WHEN S.User IN (SELECT UserId FROM Users WHERE Deleted = false) AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType,
   CASE 
      WHEN S.User IN (SELECT UserId FROM Users WHERE Deleted = false) THEN 'Here'
      ELSE 'Not'
   END AS Exist
FROM (
   SELECT *
   FROM MyTable
   WHERE Deleted = false
) as S;

I want to put the SELECT UserId FROM Users WHERE Deleted = false query into the main table as I'm using the result multiple times. In the example, I'm using the content 2 times. It's only for optimization. Hope it can really help performances.

I tried something like that:

SELECT
   Content,
   CASE 
      WHEN S.User IN (AvailableUsers) THEN 'Here'
      ELSE 'Not'
   END AS Exist,
   CASE 
      WHEN S.User IN (AvailableUsers) AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType
FROM (
   SELECT *, (SELECT UserId FROM Users WHERE Deleted = false) as AvailableUsers
   FROM MyTable
   WHERE Deleted = false
) as S;

But I get Subquery returns more than 1 row. How can I make as it works and run the query only one time?

PS: I don't have actual data as even me, while checking the query, wasn't looking for the data exactly.

Answer 1

I found a way. I'm make the check on the query of MyTable, store as variable and use it. I'm doing like that:

SELECT
   Content,
   CASE 
      WHEN S.UserAvailable = 1 THEN 'Here'
      ELSE 'Not'
   END AS Exist,
   CASE 
      WHEN S.UserAvailable = 1 AND S.Permission Like '%ADMIN%' THEN 'Admin'
      ELSE 'Basic'
   END AS UserType
FROM (
   SELECT *,
      CASE
         WHEN User IN (SELECT UserId FROM Users WHERE Deleted = false) THEN 1
         ELSE 0
      END AS UserAvailable
   FROM MyTable
   WHERE Deleted = false
) as S;

Then it works, and I'm making the request only one time