help with SPROC - JOIN ? - 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 2005 Forums

Transact-SQL (2005)

help with SPROC - JOIN ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-05-19 : 22:09:23

Hi,

I have the following SPROC, and I am trying to modify it, but not sure the best way, currently this is what it looks like

CREATE PROCEDURE [dbo].[select_logon]
(
@nameOnline varchar(50),
@password varchar(20)
)
AS SET NOCOUNT ON

SELECT tblUserDetails.userID, nameOnline, genderID,
guid, age, statusID,

'2010-10-10' as dateEnd, '20' as memberShipType, '20' as memberShipLevel FROM tblUserDetails

WHERE (nameOnline = @nameOnline and password = @password) OR (emailAddress = @nameOnline and password = @password)

I want to add 1 more column, which is the count of referring emails they have sent.

For example:

(select count(*) from tblReferEmails where emailSendCount > 0 and referUserID = @userID) as referredCount

However I am not passing the userID to the query, and am not sure the best way to determine it. (JOIN, seperate SELECT query, etc)

I can do something like :

DECLARE @userID int
SELECT @userID = userID FROM tblUserDetails where nameOnline = @nameOnline and password = @password

OR

I maybe I can do a seperate JOIN somehow ?

(tblUserDetails.userID = tblReferEmails.referUserID)

any help is greatly appreciated..

thanks once again!
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 23:42:22

[code]
SELECT u.userID,
nameOnline,
genderID,
guid,
age,
statusID,
'2010-10-10' AS dateEnd,
'20' AS memberShipType,
'20' AS memberShipLevel,
c.cnt
FROM tblUserDetails u
INNER JOIN
(
SELECT referUserID, cnt = COUNT(*)
FROM tblReferEmails
WHERE emailSendCount > 0
GROUP BY referUserID
) c ON u.userID = c.referUserID
WHERE (nameOnline = @nameOnline AND password = @password)
OR (emailAddress = @nameOnline AND password = @password)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-05-20 : 03:29:48

Hey Khtan,

This is great and brings back desired results, EXCEPT for when there are 0 rows in TBLREFEREMAILS...

When there are no rows in this table, no rows are brought back (and the login fails) I need to bring back a ZERO value instead..... I have been trying to adjust the query but not sure how .. I tried an OUTER join but no luck

any help much appreciated !

Thanks once again :D
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 03:36:36

[code]
SELECT u.userID,
nameOnline,
genderID,
guid,
age,
statusID,
'2010-10-10' AS dateEnd,
'20' AS memberShipType,
'20' AS memberShipLevel,
ISNULL(c.cnt, 0) AS cnt
FROM tblUserDetails u
LEFT JOIN
(
SELECT referUserID, cnt = COUNT(*)
FROM tblReferEmails
WHERE emailSendCount > 0
GROUP BY referUserID
) c ON u.userID = c.referUserID
WHERE (nameOnline = @nameOnline AND password = @password)
OR (emailAddress = @nameOnline AND password = @password)

[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-20 : 03:45:15

[code]WHERE @nameOnline IN (nameOnline, emailAddress)
AND @password = password[/code]

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-05-21 : 03:05:12

Hey Peso,

Nice touch on the WHERE clause :) I like it!

Is it just cleaner code? Or possibly faster too ?

Also, I am looking to make one slight modification, and have a question. Below I have modified the query to bring back 4 extra columns, but in fact I only need 1 column.

Bringing back 3 allows me to sort the data in my web application, but it would be more efficient if I sorted in the SP.

Instead of the 3 columsn (stateProv,stateProvID,country)
brought back, I want to bring back 1 column "myArea"

IF stateProvID <> 64 then I will bring back "stateProv" as "myArea"
IF stateProvID = 64 then I will bring back "country" as "myArea"

How would I structure this into the SP, is there an easy way ? Not sure I have attempted something like this before..

any help is greatly appreciated !!

thanks once again :)
mike123

SELECT u.userID,
nameOnline,
genderID,
guid,
age,
statusID,
SP.stateProv,
SP.stateProvID
C.Country,
'2010-10-10' AS dateEnd,
'20' AS memberShipType,
'20' AS memberShipLevel,
ISNULL(c.cnt, 0) AS cnt
FROM tblUserDetails u

JOIN tblStateProv SP on SP.stateProvID = u.stateProvID
JOIN tblCountry C on C.countryID = u.countryID

LEFT JOIN
(
SELECT referUserID, cnt = COUNT(*)
FROM tblReferEmails
WHERE emailSendCount > 0
GROUP BY referUserID
) c ON u.userID = c.referUserID

WHERE @nameOnline IN (nameOnline, emailAddress)
AND @password = password

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 03:19:32

I think it's somewhat easier to read. I also think SQL Engine will evaluate them as same.

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-05-21 : 05:02:49

quote:
Originally posted by Peso

I think it's somewhat easier to read. I also think SQL Engine will evaluate them as same.

E 12°55'05.63"
N 56°04'39.26"

noted, thank you! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources