one question (beginner) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

one question (beginner)

Author

Topic

pauls09
Starting Member

3 Posts

Posted - 2012-01-04 : 00:05:12

I'm experimenting with SQL Server, now I'm trying to do the following: let's say that the 'user' table has 100 entries (with id and login) and the 'login' or access table has 3000 (only id), how can I use the select command to get a list of all users that never logged in?

thanks you any help.

ntizer
Starting Member

6 Posts

Posted - 2012-01-04 : 00:15:24

u mean dat User table contains entries of users currently logged in?can u elaborate more?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-04 : 04:31:24

Please post table definitions, sample data and expected results. I can't guess what your table design looks like.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-04 : 04:45:01

Starts with this

select userid from users as u where not exists(select * from logins where userid=u.userid)

Madhivanan

Failing to plan is Planning to fail

pauls09
Starting Member

3 Posts

Posted - 2012-01-04 : 10:27:47

NeilG
Aged Yak Warrior

530 Posts

Posted - 2012-01-04 : 10:51:09

SELECT userid from usertable
except
SELECT userid from logins

This will only work in SQL server 2005 onwards

pauls09
Starting Member

3 Posts

Posted - 2012-01-04 : 11:19:23

thank you, I was reading more about SQL syntax and its much clear now.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-04 : 12:58:12

SELECT <column list> FROM users where UserID NOT IN (SELECT UserID FROM Logins)

--
Gail Shaw
SQL Server MVP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-04 : 13:19:58

Here is another way using a LEFT OUTER JOIN:

SELECT 
    <column list> 
FROM 
    Users 
LEFT OUTER JOIN
    Logins    
    ON Useers.UserID = Logins.UserID
WHERE 
    Logins.UserID IS NULL

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:26:02

Here we go again Sunita

Where's that recent post?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources