Click here to Skip to main content
14,982,408 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hii all,
Here is a Scenario first.
I have 1000+ User in my database. I want to filter the Users depending upon the existence of data in tables(Account in this case) i.e I would like to omit the Users who does not have entry in the table (NO RECORDS OF USER IN TABLE).
for example:
User Table:


Account Table:

Acc to above I would require following UserID : User001 and User002
and NOT User003.

How I can query this on Account Table...!!
Which concept will be Used.....Plzz help me on this...!

A join should work for this scenario:
--test data setup
declare @user table(
	UserId varchar(50),
	Username varchar(50)
declare @account table(
	AccountName varchar(50),
	CreateUser varchar(50),
	ModifyUser varchar(50)
insert into @user values('User001','ABC');
insert into @user values('User002','XYZ');
insert into @user values('User003','PRQ');

insert into @account values('Account1','User001','User002');
insert into @account values('Account2','User002','User001');

--join query
select distinct 
from @user u
inner join @account a
	on u.UserID = a.CreateUser or u.UserID = a.ModifyUser
lovejeet0707 12-Jan-15 3:52am
Worked for me...!
thx :)
jaket-cp 12-Jan-15 4:05am
glad to help :)
You can do it like this, supposing that you need to check both CreateUser and ModifyUser:
select from [user] u where exists(select * from account a where u.CreateUser = a.UserID or u.ModifyUser = a.UserID)
deepakdynamite 9-Jan-15 7:17am
This will be a performance hit...
Zoltán Zörgő 9-Jan-15 8:57am
I doubt. Exists * is optimized.
deepakdynamite 11-Jan-15 23:22pm
use it like

Exists ( Select top 1 1 from account a ......)

Try this out :)

still I would suggest to use joins because if you look at SQL engine... It will fire inner query (query that resides inside Exists) each time.. where as Joins will be handled diffently...
lovejeet0707 12-Jan-15 3:53am
as i have to check against whole using top wont work....!
Zoltán Zörgő 12-Jan-15 5:40am
Of course, joining will work. But as mentioned, exists * has it's own optimization under the hood, so it has also good performance. Hare you can see some benchmark: So you can freely use my approach too if it is more convenient. Using TOP would also work, but might even harm...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900