View Single Post
  #3 (permalink)  
Old April 10th, 2010, 03:05 PM
sg48 sg48 is offline
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default Not sure I agree

Thanks for your response.

When I watch BuyersAgent it has the correct value, so it seems that it does exist.

Here is my stored procedure below

What I am trying to accomplish is the fo0llowing:
I have a tblPerson with all the person data.

I have several other tables that are involved in the stored procedure. In the tblOffer I have several 'keys' that are pointers to the tblPerson,- ie BuyersAgentPersonKey, Purchaser1PersonKey.

I am trying not to put the person data into the tblOffer directly, only the keys.

Then I want the stored procedure to stuff all of the data including the person data into the GridView.



Code:
ALTER PROCEDURE dbo.procGetNegotiationsByIDSelect
/*
don't know how to handle multiple datatables
*/
(
@dealid int = '1'
) 
AS
SELECT 
tblDeal.DealID, 
tblDeal.DealName, 
tblDeal.StartDate, 
tblDeal.ContractDate, 
tblDeal.ClosingDate, 
tblOffer.SubmitRole, 
tblOffer.Status, 
tblOffer.OfferNumber, 
tblOffer.OfferType, 
tblOffer.OfferDateTime, tblOffer.PctDown, tblOffer.OfferAmount, 
tblOffer.SuggestedClosingDate, 
tblOffer.CompletePct, tblOffer.Guarantor, 
tblOffer.Contingencies, tblOffer.Notes, 
tblDeal.DealShortName,
tblOffer.mostrecent, tblOffer.OfferID,
tblProperty.StreetAddress1, 
tblProperty.StreetAddress2, tblProperty.City, tblProperty.State, 
tblProperty.Country, tblProperty.PostalCode

FROM tblDeal INNER JOIN
tblNegotiation ON tblDeal.DealID = tblNegotiation.DealKey INNER JOIN
tblOffer ON tblDeal.DealID = tblOffer.DealKey INNER JOIN
tblProperty ON tblDeal.PropertyKey = tblProperty.PropertyID 
WHERE (tblDeal.DealID = @dealid)
SELECT P1.FName + ' ' + P1.LName AS [Purchaser1],

A.FName + ' ' + A.LName AS [BuyersAgent]
FROM tblOffer AS OT INNER JOIN 
tblPerson as P1 ON P1.PersonID = OT.Purchaser1PersonKey INNER JOIN

tblPerson AS A ON A.PersonID = OT.BuyersAgentPersonKey
WHERE OT.DealKey=@dealid