Link to home
Start Free TrialLog in
Avatar of Mark Bakelaar
Mark BakelaarFlag for Norway

asked on

Select in select

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
Avatar of jogos
Flag of Belgium image

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

Avatar of Anuradha Goli
Anuradha Goli
Flag of Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial