this is the meat of the SP
@value_needed is the value I need to get. it needs to be a.adviceID from the
first select.
SELECT
a.userIDFK, a.adviceID,
UD.userPhotoPath AS adviceseekerPhotoPath,
UD.userPhoto AS adviceSeekerPhoto,
UD.userSensitivity AS adviceSeekerSensitivity,
UD.userCareer AS adviceSeekerCareer,
UD.userHumor AS adviceSeekerHumor,
UD.userPersonality AS adviceSeekerPersonality,
UD.userIntelligence AS adviceSeekerIntelligence,
UD.userDescription AS adviceseekerdescription
FROM Advice a INNER JOIN userData UD on A.userIDFK = UD.UserID
WHERE a.userIDFK = @Input_userID
-- select data on "the other people"
SELECT
aa.Advice_aboutID, aa.AdviceIDFK, aa.PhotoDescription,
aa.adviceSensitivity, aa.adviceCareer, aa.adviceHumor, aa.advicePersonality,
aa.adviceIntelligence, aa.PhotoPath, aa.Photo
FROM Advice_about aa
WHERE aa.adviceIDFK = @value_needed
-- select existing ratings
SELECT
ar.adviceIDFK, ar.ratingUserID, ar.adviceFeedback, ar.rating,
UD.userLogin
FROM Advice_ratings ar LEFT OUTER JOIN userData UD on ar.ratingUserID =
UD.UserID
WHERE ar.adviceIDFK = @value_needed
GROUP BY ar.ratingUserID, UD.userLogin, ar.adviceIDFK, ar.adviceFeedback,
ar.rating
On 12/8/05, Adrian Lynch <[EMAIL PROTECTED]> wrote:
>
> Got some code to show?
>
> -----Original Message-----
> From: John Wilker [mailto:[EMAIL PROTECTED]
> Sent: 08 December 2005 22:42
> To: CF-Talk
> Subject: Another SQL question. Stored Proc this time
>
>
> I have a proc that does some selects. The first select is driven by a
> passed
> in UserID. THe next is driven by a foriegn key found in the first select.
>
> New hurdle in SPs for me. How do I get the FK I find in select 1 to select
> 2
>
> I tried declaring a local @var, then setting it to that column, but it
> doesn't know what that column is.
>
> Thoughts.
>
> J
>
> --
> John Wilker
> Writer/Web Consultant
> www.johnwilker.com / www.red-omega.com
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226634
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
