Avatar of Mark Bakelaar
Mark Bakelaar
Flag 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Anuradha Goli

8/22/2022 - Mon
jogos

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

ASKER CERTIFIED SOLUTION
Anuradha Goli

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck