I think I have this right. The stored procedure for the bug search is designed to return records even if all the search criteria is not met. For example: If you search for a bug Status of New and also Open, it will display a bug that is new and not open because one of the criteria was met. For my needs it should only display a bug that absolutely meets all of the criteria highlighted in the list boxes. If a bug is Open, but not New, then it should not appear on the list if the criteria was for Open and New. I know that this can probably be done by modifying the stored procedure but it is beyond my knowledge of sql. Am I correct? Any ideas? Thanks in advance and thanks for a great book!

This may be a lot easier to fix than you think.

The values for properties like Status are mutually exclusive. So, a bug cannot be New and Open at the same time.

To make sure you only get the data that you require, simple disable multiple selection on the drop down list. This way, you can only select a single status and thus get the records you require.

Does this help?

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

I explained that poorly. I am modifying the bugbase to keep track of attorneys instead of bugs (I know, I know, whats the difference) and allowing the user to select multiple items that are not mutually exclusive. I am using tables with one to many relationships. Example, the main table is called attorney, the languages the attorney speaks are stored in AttorneyLanguages, the locations the attorney will go to are stored in AttornyLocations and so on. The stored procedure is below if that is helpful. Thanks so much for the quick reply Imar. By the way, do you have a new book coming soon?



CREATE PROCEDURE [dbo].[sprocAttorneySearchList]

@keyword nvarchar(50) = null,
@panels nvarchar(50) = null,
@languages nvarchar(50) = null,
@locations nvarchar(50) = null,
@specialNeeds nvarchar(50) = null,
@status nvarchar(10) = null

AS
SELECT DISTINCT Attorney.Id, Attorney.FirstName, Attorney.LastName, Attorney.Status

FROM

Attorney

LEFT OUTER JOIN AttorneyPannels ON Attorney.Id = AttorneyPannels.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@panels, ',') AS joinPanels ON AttorneyPannels.PanelId LIKE joinPanels.value
LEFT OUTER JOIN AttorneyLanguages ON Attorney.Id = AttorneyLanguages.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@languages, ',') AS joinLanguages ON AttorneyLanguages.LanguageId LIKE joinLanguages.value
LEFT OUTER JOIN AttorneyLocations ON Attorney.Id = AttorneyLocations.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@locations, ',') AS joinLocations ON AttorneyLocations.LocationId LIKE joinLocations.value
LEFT OUTER JOIN AttorneySpecialNeeds ON Attorney.Id = AttorneySpecialNeeds.AttorneyId
LEFT OUTER JOIN dbo.fnSplit(@specialNeeds, ',') AS joinSpecialNeeds ON AttorneySpecialNeeds.SpecialNeedId LIKE joinSpecialNeeds.value




WHERE (AttorneyPannels.PanelId LIKE joinPanels.value OR @panels IS NULL)
AND (Attorney.Status LIKE @status OR @status IS NULL)
AND (AttorneyLanguages.LanguageId LIKE joinLanguages.value OR @languages IS NULL)
AND (AttorneyLocations.LocationId LIKE joinLocations.value OR @locations IS NULL)
AND (AttorneySpecialNeeds.SpecialNeedId LIKE joinSpecialNeeds.value OR @specialNeeds IS NULL)
AND (Attorney.LastName LIKE '%' + @keyword + '%' OR Attorney.FirstName LIKE '%' + @keyword + '%' OR @keyword IS NULL)

Right, that's indeed a different story.

If I understand the situation correctly, look into INNER JOIN instead of LEFT OUTER JOIN. The latter returns all records from the left table, regardless of whether there's a match with the right table.

Using an INNER JOIN gives you only those records that have a match on both sides. So, giving a split string with two record IDs should give you the two records in the matching junction table.

Yes, working on a new project. Will post an update on that pretty soon on my own blog.

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Thanks again. I guess the best way to describe what I am after is this: If one of the comma-delimited lists that I am sending to the stored procedure has 3 items in it, and I only have a match for 2 of those items, I should end up with an empty record set, not 2 records. I think I need to find a different solution, but I wanted to use stored procedures becuase I am just learning them. Thanks again.

John

Yeah, this is the kind of problem that you need to approach step by step. Remove 90% of the code and then start adding things back in.

It's not an easy requirement, and it's not something I can do over a P2P post. Sorry...

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004