Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Need help with nested sql

    I have searched the forum before posting this and although I seen multiple nested sql questions, none were like mine.

    This is an ASP.net app written in C#, Database is sql 2k

    I have a stored procedure that is used with a Search form we have. Search form has about 15 parameters it can take, all optional.

    The database is a mess and im stuck working around it so bare with me while I try to explain my issue. There are more issues but want to focus on the Buyer/Seller issue for now.

    The long and short of my problem is this - the data entry form has fields for { Buyer First Name, Buyer Last Name, Seller First Name, Seller Last Name}

    These will be inserted into table BuyerSeller (for instance)
    The schema for this:
    BuyerSellerID | FirstName | LastName

    this table holds both Buyer and Seller information. So what we end up with is:
    BuyerSellerID | FirstName | LastName
    100 | BuyerFirst | BuyerLast
    101 | SellerFirst | SellerLast

    But now the issue comes into play when he created an index lookup table called BuyerSellerInfo that includes:
    BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID

    What the original developer did was put the Buyer information as one record, and the Seller information in another record, each for the same data entry.

    so we end up with:

    BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID
    1 | 10 | 1 | NULL
    2 | 10 | NULL | 2

    The formatting of this is messed up I know. But what this shows is that there are two records for the same MortgageId - on different rows. They should be on the same row (i.e 1 | 10 | 1 | 2)

    So now with my question - how can I create a stored procedure that will produce results in the same row. I am getting duplicates for each row (which I know the way it is setup I would). But is there a way in my Stored Procedure to have the output look like:

    BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID
    1 | 10 | 1 | 2

    Hope this made sense, question me if it does not. The formatting on my code tags didn't go so well the "|" is seperator for columns.

    I hope this doesn't make the post too long, but here is my current stored procedure:
    Code:
    SELECT dbo.Mortgage.MortgageId, T1.Agent, T1.Agency, T1.InsCompanyUW,
                       (SELECT DISTINCT FirstName + ' ' + LastName AS Buyer
                        FROM   dbo.BuyerSeller
                        WHERE (BuyerSellerId = BSI.BuyerId) AND (FirstName IS NOT NULL) AND (LastName IS NOT NULL)) AS Buyer,
                       (SELECT BuyerSeller_1.FirstName + ' ' + BuyerSeller_1.LastName AS Seller
                        FROM   dbo.BuyerSellerInfo LEFT OUTER JOIN
                                       dbo.BuyerSeller AS BuyerSeller_1 ON BuyerSeller_1.BuyerSellerId = dbo.BuyerSellerInfo.SellerId
                        WHERE (dbo.BuyerSellerInfo.MortgageId = @MortgageId) AND (BuyerSeller_1.FirstName + ' ' + BuyerSeller_1.LastName IS NOT NULL)) AS Seller, 
                   L1.BrokerageBus, L1.Originator, L1.Mortgagee, RE1.Appraiser, RE1.BuyersBroker, RE1.BuyersSalesperson, P1.StreetAddress, P1.City, 
                   CONVERT(varchar, dbo.Mortgage.TransClosingDate, 101) AS TransClosingDate, P1.County
    FROM  dbo.Mortgage INNER JOIN
                   dbo.BuyerSellerInfo AS BSI ON dbo.Mortgage.MortgageId = BSI.MortgageId LEFT OUTER JOIN
                   dbo.RealEstateInfo AS RE1 ON dbo.Mortgage.MortgageId = RE1.MortgageId AND RE1.Appraiser IS NOT NULL AND 
                   RE1.BuyersBroker IS NOT NULL AND RE1.BuyersSalesperson IS NOT NULL LEFT OUTER JOIN
                   dbo.TitleInfo AS T1 ON dbo.Mortgage.MortgageId = T1.MortgageId AND T1.Agent IS NOT NULL AND T1.Agency IS NOT NULL AND 
                   T1.InsCompanyUW IS NOT NULL LEFT OUTER JOIN
                   dbo.PropertyInfo AS P1 ON dbo.Mortgage.MortgageId = P1.MortgageId AND P1.StreetAddress IS NOT NULL AND P1.City IS NOT NULL 
                   LEFT OUTER JOIN
                   dbo.LoanInfo AS L1 ON dbo.Mortgage.MortgageId = L1.MortgageId AND L1.BrokerageBus IS NOT NULL AND L1.Originator IS NOT NULL AND 
                   L1.Mortgagee IS NOT NULL
    WHERE (dbo.Mortgage.MortgageId = @MortgageId)
    I get what I showed above - two records with the only difference being the Seller information is in the second row with the rest being null, the first row has everything it is suppose to except the Seller Information.

    Also note that to fix this I tried using a Select Statement in the For Clause, this did narrow it down to the TWO rows, before I wasn't even getting that.

    Any suggestions would be great.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Welborn10 View Post
    so bare with me
    Woo hoo!

    Code:
    SELECT BuyerorSellerInfoID, MortgageID, MAX(BuyerID) AS buyer,  MAX(SellerID) AS seller
    FROM myTable
    GROUP BY BuyerorSellerInfoID, MortgageID

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Hey troubleshooting a problem isn't a challenge unless done in the buff

  4. #4
    Join Date
    Mar 2010
    Posts
    4
    Thanks for the sql as well. Was this to replace the Select statements I nested inside the Select? If not where did you envision me putting this or replacing?

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Duplicate Post
    Last edited by Welborn10; 03-16-10 at 14:34.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I didn't look at your SQL so I don't know. I read your post (well written) that described the problem and stated your desired output. It should produce this bit:
    Quote Originally Posted by Welborn10 View Post
    is there a way in my Stored Procedure to have the output look like:

    BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerID
    1 | 10 | 1 | 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •