Check for both user availability

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check for both user availability

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 06:48:03

Hi,

How to check if both user name are available in a table

SELECT * FROM Users WHERE Users.UserName = 'babu' and Users.UserName = 'pythondave'

gives me wrong result.

since it checks both 'babu' and 'pythondave' avilable in each row.

SELECT * FROM Users WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'

It satisfy if any one of them present.

Help me to solve.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 07:29:27

select UserName from Users
WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having count(distinct UserName)=2

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:32:00

quote:
Originally posted by madhivanan

select UserName from Users
WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having count(distinct UserName)=2

Madhivanan

Failing to plan is Planning to fail

grouping by username and still taking count() of it? this will return only 1 always

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:32:49

is it this what you want
SELECT * FROM Users WHERE Users.UserName IN( 'babu','pythondave')

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 07:36:07

Hi,

SELECT UserName FROM Users WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having count(distinct UserName) = 1

It is not correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:36:34

or this?


SELECT CASE WHEN COUNT(DISTINCT CASE WHEN Users.UserName = 'babu' OR Users.UserName = 'pythondave'
 THEN Users.UserName ELSE NULL END)=2 THEN 'Present' ELSE 'Not Present' END FROM Users

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 07:37:39

quote:
Originally posted by visakh16

is it this what you want
SELECT * FROM Users WHERE Users.UserName IN( 'babu','pythondave')

I want to check both user must available.

IN clause gives result if any one is available.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 07:38:33

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

select UserName from Users
WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having count(distinct UserName)=2

Madhivanan

Failing to plan is Planning to fail

grouping by username and still taking count() of it? this will return only 1 always

Yes


declare @test table(Username varchar(100))
insert into @test 
select 'test' union all
select 'babu' union all
select 'pythondave' 

select Username from
(
	select UserName,count(*) over () as counting from @test
	where UserName = 'babu' OR UserName = 'pythondave'
) ast 
where counting=2

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 07:42:58

Hi madhivanan,

Thaks for your reply.

It worked well.

This also worked

SELECT @@rowcount, UserName FROM Users WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having @@rowcount = 2

Bye.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 07:48:47

quote:
Originally posted by baburk

Hi madhivanan,

Thaks for your reply.

It worked well.

This also worked

SELECT @@rowcount, UserName FROM Users WHERE Users.UserName = 'babu' OR Users.UserName = 'pythondave'
group by UserName
having @@rowcount = 2

Bye.

@@ROWCOUNT method wont work properly


declare @test table(Username varchar(100))
insert into @test 
select 'test' union all
select 'babu' union all
select 'pythondave' 

SELECT @@rowcount, UserName FROM @TEST WHERE UserName = 'babu' OR UserName = 'pythondave'
group by UserName
having @@rowcount = 2

select Username from
(
	select UserName,count(*) over () as counting from @test
	where UserName = 'babu' OR UserName = 'pythondave'
) ast 
where counting=2

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 08:03:42

Hi madhivanan,

Since username that I am passing is dynamic

so help for this.

DECLARE @CombineUserNames VARCHAR(1000)
DECLARE @NoOfUsers INT
BEGIN

SELECT @NoOfUsers = count(*) FROM dbo.[Split](',' , @UserNames)

SELECT @CombineUserNames = COALESCE(@CombineUserNames + ' OR ', '') + 'UserName = ' + '''' + Item + '''' FROM dbo.[Split](',' ,@UserNames)

select Username from
(
EXEC( 'SELECT count(*) over () as counting, UserName FROM dbo.Users
WHERE ' + @CombineUserNames )
)ast
where counting=@NoOfUsers

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 08:11:35

[code]declare @UserNames varchar(100)
set @UserNames='babu,pythondave'
select Username from
(
SELECT count(*) over () as counting, UserName FROM dbo.Users
WHERE ','+@UserNames+',' like '%,'+username+',%'
)as t
where counting=(len(@UserNames)-len(replace(@UserNames,',','')))+1
[/code]

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-14 : 08:17:01

Hi madhivanan,

Thanks u very much.

look at this link also.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114469

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources