Getting SP results from a table with multiple rows for same ID [modified]
-
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 is holds both Buyer and Seller information. So what we end up with is:
BuyerSellerID | FirstName | LastName 100 | BuyerFirst | BuyerLast 101 | SellerFirst | SellerLastBut now the issue comes into play when he created an index lookup table called BuyerSellerInfo that includes:BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerIDWhat 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 | 2So 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. The database is SQL 2000 as well. Thanks.modified on Friday, March 12, 2010 4:02 PM
-
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 is holds both Buyer and Seller information. So what we end up with is:
BuyerSellerID | FirstName | LastName 100 | BuyerFirst | BuyerLast 101 | SellerFirst | SellerLastBut now the issue comes into play when he created an index lookup table called BuyerSellerInfo that includes:BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerIDWhat 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 | 2So 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. The database is SQL 2000 as well. Thanks.modified on Friday, March 12, 2010 4:02 PM
I'm no DB expert, however I would search along these lines:
SELECT * FROM BuyersSellers AS b JOIN BuyersSellers AS s WHERE b.MortgageID=s.MortgageID AND b.BuyerID IS NOT NULL AND s.SellerID IS NOT NULL
:)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
-
I'm no DB expert, however I would search along these lines:
SELECT * FROM BuyersSellers AS b JOIN BuyersSellers AS s WHERE b.MortgageID=s.MortgageID AND b.BuyerID IS NOT NULL AND s.SellerID IS NOT NULL
:)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
-
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 is holds both Buyer and Seller information. So what we end up with is:
BuyerSellerID | FirstName | LastName 100 | BuyerFirst | BuyerLast 101 | SellerFirst | SellerLastBut now the issue comes into play when he created an index lookup table called BuyerSellerInfo that includes:BuyerorSellerInfoID (primary key) | MortgageID | BuyerID | SellerIDWhat 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 | 2So 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. The database is SQL 2000 as well. Thanks.modified on Friday, March 12, 2010 4:02 PM
Swelborn wrote:
so bare with me while I try to explain
Have you been reading The Nudist On The Nightshift? :-D
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis
-
Swelborn wrote:
so bare with me while I try to explain
Have you been reading The Nudist On The Nightshift? :-D
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis