Need help with nested sql

Welborn10 · #1 (**permalink**) 03-16-10, 12:19

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.

pootle flump · #2 (**permalink**) 03-16-10, 12:24

Quote:

Originally Posted by Welborn10

so bare with me

Woo hoo!

Code:

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

Welborn10 · #3 (**permalink**) 03-16-10, 12:56

Hey troubleshooting a problem isn't a challenge unless done in the buff

Welborn10 · #4 (**permalink**) 03-16-10, 13:02

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?

Welborn10 · #5 (**permalink**) 03-16-10, 13:07

Duplicate Post

pootle flump · #6 (**permalink**) 03-16-10, 15:51

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

is there a way in my Stored Procedure to have the output look like:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech