3 Table Join in Stored Proc

Question

Post reply

3 Table Join in Stored Proc

Larry Bargers

SSC Journeyman

Points: 81
More actions
January 15, 2010 at 7:58 am

#386113

Hi,
I need to write a STORED PROC that retrieves data to a single select statment based on three tables. The first table "FeatureRequests" contains a UserID of a user that submitted the request. The next table "FeatureRequestComments" contains userId's of all the users that have commented on the request. The last table "Users" contains the email address and UserId of all users. I wrote a query that retrieves these into 2 columns. But my objective is to get them into a single column, heres what I currently have:
ALTER PROCEDURE [dbo].[Bold_FeatureRequest_GetUsersByFeatureId]
@FeatureId INT
AS
SELECT U.Email, U2.Email
FROM Bold_FeatureRequestComment FRC
JOIN Users U on FRC.UserId = U.UserID
JOIN Bold_FeatureRequest FR on FRC.featureId = FR.featureID
JOIN Users U2 on FR.UserId = U2.UserId
WHERE FRC.FeatureId = @featureId
Any ideas on how to do this? The purpose of this stored proc, is to return all users that have commented on a feature request, as well as the person who submitted the request. Then I take that result list and send off a notification email to all those users.
Thanks,
Larry B.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT u.Email, 'Submitted by' AS UserRole

FROM Bold_FeatureRequest FR

INNER JOIN Users U on FR.UserId = U.UserId

WHERE FR.FeatureId = @featureId

UNION ALL

SELECT u.Email, 'Commented on by' AS UserRole

FROM Bold_FeatureRequestComment FRC

INNER JOIN Users U on FRC.UserId = U.UserID

WHERE FRC.FeatureId = @featureId

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

What would help is if you would post the table definition(s) (CREATE TABLE statement(s)), some sample data (series of INSERT INTO statements for the table(s)), and the expected results of the query you are trying to write (what you want to see, not what you are currently getting).

Larry Bargers SSC Journeyman Points: 81 More actions · Answer 3

Thanks Chris that was exactly what I needed to get the UserId's.

In retriving those UserID's on the vb.net side, I use an executeReader to fill a collection of (Int32). The problem is they now come up as 0's. Do I have to return something from the STORED proc?

Larry Bargers SSC Journeyman Points: 81 More actions · Answer 4

I got it working by adding an Info class and populating that.

Thanks again for the help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Larry Bargers (1/15/2010)
I got it working by adding an Info class and populating that.
Thanks again for the help.

You're welcome, Larry, good result.

Can I recommend that for future reference, you read the excellent articles which Lynn has in his sig?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden