SQL Query if data exist in table or not.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 8-Jan-15 23:21pm

lovejeet0707

Add a Solution

2 solutions

Solution 1

You can do it like this, supposing that you need to check both CreateUser and ModifyUser:

SQL

select from [user] u where exists(select * from account a where u.CreateUser = a.UserID or u.ModifyUser = a.UserID)

Posted 8-Jan-15 23:27pm

Zoltán Zörgő

Updated 9-Jan-15 0:54am

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...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

jaket-cp · Accepted Answer · 2015-01-09T00:50:00

A join should work for this scenario:

SQL

--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
;