help with query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-02 : 01:09:39

Hi,

I have a SPROC I'm working on but not exactly sure how to write it. I have 2 tables

UserDetails
userID,emailAddress

and

Email_Invites
userID,emailAddress

When a user invites friends, the emailaddress are stored in "Email_Invites". I would like to select all records in "UserDetails" that have the emailaddresses included in "Email_Invites".

Below is a start, if anyone can help its much appreciated.

Thanks again!!!
mike123

CREATE PROCEDURE dbo.select_Invite_FriendsAlreadyExist
(
@userID int,
@emailAddress varchar(50)
)
AS SET NOCOUNT ON

SELECT * FROM UserDetails WHERE emailAddress = @emailAddress

WHERE exists ? tblReferEmails

GO

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-02 : 01:48:07

So... you want to know which people that have been invited already exist as users?

select distinct ud.*
from UserDetails ud
JOIN
Email_Invites ei
ON ei.emailAddress = ud.emailAddress

I've only thrown distinct in there because you only want one record each, right?

You could use:

select * from UserDetails ud where exists (select * from Email_Invites ei where ei.emailAddress = ud.emailAddress)

But it really depends on what you want. If you wanted to count how many times each one has been invited, you could 'group by' and 'count(*)' in the first query.

Am I undersstanding you right?

Rob

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 01:48:48

Is this what you want ?

select *
from   UserDetails u inner join Email_Invides e
       on u.UserID = e.UserID
where  e.emailAddress = @emailAddress

]

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-02 : 01:54:03

I don't think it is.

I think the situation is...

userid = khtan
emailaddress = khtan@somewhere.com

You invite people:

userid = khtan
emailaddress = khtansfriend@somewhereelse.com

userid = khtan
emailaddress = khtansfriend2@somewhereelse2.com

And he wants to see if your friends are already in the system.

Rob

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-02 : 02:01:23

Hey Rob,

Great first post, welcome to the board ! ;) and thanks for the quick reply, looks like it is working great and running fairly quick too.

I have another SPROC that I am writing very similar, using the same 2 tables. It accomplishes the opposite.

From the record of emails I have, I would like to select all the records in "Email_Invites" that do not have a corresponding record in "UserDetails". Basically all the users that have been invited, that have not joined.

thanks khtan too, but I think rob hit it right :)

Thanks very much for any help!

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-02 : 02:07:10

I was surprised it was my first post. I registered here long ago - I think it's just one of those sites that's not in my 'every day' list of sites. I'll have to try to visit more often.

Rob Farley
http://robfarley.blogspot.com

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-02 : 02:10:20

well you obviously know your stuff regardless :)

would you know or anyone else know how to accomplish this part?

I have another SPROC that I am writing very similar, using the same 2 tables. It accomplishes the opposite.

From the record of emails I have, I would like to select all the records in "Email_Invites" that do not have a corresponding record in "UserDetails". Basically all the users that have been invited, that have not joined.

Thanks again!!!!
mike123

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-02 : 02:21:14

Ah - that's even easier.

Use a left join, but look for the null records in the other table. This is typically the best way of doing a 'not exists'.

select ei.*
from Email_Invites ei
LEFT JOIN
UserDetails ud
ON ei.emailAddress = ud.emailAddress
where ud.emailAddress is null

Rob Farley
http://robfarley.blogspot.com

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-02 : 02:32:59

thanks a bunch!! my night will be productive now !

have a great one ,

mike

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 05:26:28

Just for completeness... The 'not exists' query Rob supplied, can also be written as...

select * from Email_Invites ei where not exists
    (select * from UserDetails where emailAddress = ei.emailAddress)

select * from Email_Invites where emailAddress not in
    (select emailAddress from UserDetails)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources