Join - optional criteria

Question

Join - optional criteria

norbertackerman

SSC Eights!

Points: 945
More actions
May 18, 2007 at 10:03 am

#195747

I have a stored proc that returns results based on an integer variable input.
In the where clause I would like this criteria to be optional : user_pic.user_pic_default = 'y'
I would like to still show a result for a postid that does not have user_pic.user_pic_default equal 'y'

ALTER PROCEDURE [dbo].[usp_getComments] --usp_getComments 116
(@postid int)

AS
BEGIN
SET NOCOUNT ON;
SELECT     comment.comment_id, comment.comment, comment.post_id, comment.comment_date, aspnet_Users.UserId, aspnet_Users.UserName,
                      user_pic.user_pic, user_pic.user_pic_default
FROM         comment INNER JOIN
                      aspnet_Users ON comment.userid = aspnet_Users.UserId LEFT OUTER JOIN
                      user_pic ON aspnet_Users.UserId = user_pic.userid
WHERE     (user_pic.user_pic_default = 'y') AND (comment.post_id = @postid)
END

Thank you,
Norbert
meLearnASP.net

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

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

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

A little more specific on what you mean optional. What exactly do you want returned from this stored proc? Some sample data and results would help. My suggestion without any additional info would be change your AND to an OR.

norbertackerman SSC Eights! Points: 945 More actions · Answer 2

Sorry for not being clear.

I would like all results to return for Comment.post_id = @postid regardless if user_pic.user_pic_default = 'y'

So if there is a row that matches the post_id but does not have 'y' in user_pic.user_pic_default I still want it to be in the result set.

Thank you,

Norbert

meLearnASP.net

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

If I understand you correctly, then just remove the "(user_pic.user_pic_default = 'y') AND" from your WHERE clause.

This will return ALL records for the postId regardless of the default value.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

norbertackerman SSC Eights! Points: 945 More actions · Answer 4

Jason - thanks for the answer.

Actually this what I had in mind.

If there is a user_pic_default = 'y' for the post_id then in the result, return the value for user_pic.user_pic otherwise return null for that field.

This would be a return of the stored procedure

1 | Comment 1 | 116 | 2007-05-11 09:53:26.263 | 4852D246-BA34-443C-9B53-C950F8C8DA17 | username | pic123 | y

Thank you,

Norbert

meLearnASP.net

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

Move the check into the join clause....

SELECT comment.comment_id, comment.comment, comment.post_id, comment.comment_date, aspnet_Users.UserId, aspnet_Users.UserName,

user_pic.user_pic, user_pic.user_pic_default

FROM comment INNER JOIN

aspnet_Users ON comment.userid = aspnet_Users.UserId LEFT OUTER JOIN

user_pic ON aspnet_Users.UserId = user_pic.userid

AND user_pic.user_pic_default = 'y'

WHERE comment.post_id = @postid

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

norbertackerman SSC Eights! Points: 945 More actions · Answer 6

Jason,

Perfect. This is exactly what I was looking for.

Thank you for the great solution.

Norbert

meLearnASP.net