help with query (where doesnt exist)

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)

help with query (where doesnt exist)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-24 : 22:41:14

Hi,

I have a "users" table in which I want to SELECT all users without mail. I'm not exactly sure about the best way to write this, and I think having two tables makes it more difficult to decide. Should I union the tables and select from that ? two different selects? I'm looking to write this the fastest way possible obvoiusly :)

I'm not exactly sure the syntax for doing it either.. any help is very much appreciated ! :)

Thanks once again!!
mike123

SELECT * FROM tblUserDetails WHERE ..

Basically these two conditions below have to be 0, or perhaps its better to do a not exists...

SELECT count(*) FROM tblInstantMessage WHERE messageToID = @messageToID
SELECT count(*) FROM tblMessage WHERE messageToID = @messageToID




CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[NameOnline] [varchar](15) NULL
)






CREATE TABLE [dbo].[tblInstantMessage](
	[InstantMessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageToID] [int] NULL,
	[MessageFromID] [int] NULL,
	[Message] [varchar](750) NULL,
)




CREATE TABLE [dbo].[tblMessage](
	[MessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageFromID] [int] NOT NULL,
	[MessageToID] [int] NOT NULL,
	[Message] [varchar](1500) NULL
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-24 : 22:49:49

[code]
select *
from tblUserDetails u
where not exists
(
select *
from tblInstantMessage
where MessageToID = u.UserID
)
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-24 : 23:58:41

hey Khtan,

Thanks! I have two tables I need to check tho,

should I just have

select *
from tblUserDetails u
where not exists
(
select *
from tblInstantMessage
where MessageToID = u.UserID
)
and not exists
(
select *
from tblMessage
where MessageToID = u.UserID
)

or maybe I should union the tblInstantMessage and tblMessage tables ?

I assume the first way is probably better.. but I'm eager to hear any comments..

thanks again ! :)
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 00:17:19

[code]
select *
from tblUserDetails u
where not exists
(
select *
from tblInstantMessage
where MessageToID = u.UserID
)
and not exists
(
select *
from tblMessage
where MessageToID = u.UserID
)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources