StealthRT StealthRT - 2 months ago 9
SQL Question

STUFF in MS SQL query that I need to query within it using AND

I hope this question won't be too hard to understand but here goes.

I have a query that uses the STUFF() operator that is set AS Roles. The output for Roles is:

Main Administrator, Per Admin, Coordinator


Now I wish to use this Roles within my querys' AND properties like so:

Select
*, STUFF(blah blah...) AS Roles
FROM
table AS T1
INNER JOIN table2 AS T2
ON T1.user = T2.user
Where
....
AND
Roles = 'Per Admin'


However, there are 2 things wrong.

1) I am not able to use the "Roles" within the AND operator stating that the error is 'Invalid column name 'Roles'.

2) Once I get #1 above working, I need to be able to find Per Admin within those listed Roles. I am thinking it might involve some type of string_split or something similar?

Help would be great from a SQL Guru :)

Answer

you can add EXISTS to your query and duplicate the STUFF query or something similar and add your filter in the EXISTS.

Select 
    *, STUFF(blah blah...) AS Roles 
 FROM
    table AS T1 
 INNER JOIN table2 AS T2
    ON T1.user = T2.user 
 Where 
    .... 
 AND 
    EXISTS (SELECT 1 FROM blahblah WHERE RoleName = 'Per Admin')