Select in select

Mark Bakelaar
Mark Bakelaar used Ask the Experts™
on
Dear Experts,

I need to have a where statement similar to below, but I am not able to get it to work.

Can you please help me with this one.
Thanks, MB

DECLARE @UserName as varchar(59)
SET @UserName ='mb'

SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole] , [OwnCompanyID]
FROM [Users]
WHERE [CompanyID] = OwnCompanyID of @UserName
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
If you are 100% sure a user has only 1 company
WHERE [CompanyID] = 
 (select OwnCompanyID 
 from dbo.tableUserCompany  -- change tablename to the one in your database
 where UserName = @UserName
) 

Open in new window

But more safe
WHERE exists  
 (select 1  
 from dbo.tableUserCompany  as uc -- change tablename to the one in your database
 where uc.UserName = @UserName
 and  uc..[CompanyID] = [Users].[CompanyID]
) 

Open in new window

Systems Development / Support Specialist
Commented:
DECLARE @UserName as varchar(59)
SET @UserName ='mb'

SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole] , [OwnCompanyID]
FROM [Users]
WHERE [CompanyID] in (select OwnCompanyID FROM [tablename] where UserName = @UserName)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial