Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
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:

UserID---------Username
User001--------ABC
User002--------XYZ
User003--------PQR
...................

...................
Account Table:
AccountName-------CreateUser-------ModifyUser
Account1-----------User001---------------User002
Account2-----------User002---------------User001

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...!
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

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 
	u.* 
from @user u
inner join @account a
	on u.UserID = a.CreateUser or u.UserID = a.ModifyUser
;
   
v2
Comments
lovejeet0707 12-Jan-15 3:52am
   
Worked for me...!
thx :)
jaket-cp 12-Jan-15 4:05am
   
glad to help :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

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)
   
v2
Comments
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 table....so 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: http://explainextended.com/2009/06/16/in-vs-join-vs-exists/. 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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100