Ok I am not sure how to handle this query. Your help would be greatly appreciated.
I am trying to return a bunch of search results which are more or less strait forward, except that part of the results I am trying to get at could be multiple records in a related table. i'll try and explain:
I have a main table which stores info about a Photographer - this table relates to another which has a list of their specialties - so it looks like this :
Specialties Table
PhotographerID SpecialtyID
1 1
1 2
1 3
2 1
2 2
Now, here's the catch a person doing a search is allowed to search for more than one specialty - so I am trying to pass this to the SP as a comma deliminated string (1,2,3) So the search is looking for a Photographer who specializes in these 3 specialties. If it were just one then I could do a sub query - but how do you handle it in this case? I have used CharIndex in the past, but that was comparing a deliminated string against one value, where here there may be several.
Here's my attempt so far. But this only works if I pass one specialtyID and then I have to use Distinct as well
Alter PROCEDURE "sp_PagedItems_Listings"
(
@Creator nvarchar(80) = null,
@DiciplineID int,
@SpecialtyID int, - this should probably be an nvarchar with the direction I am heading
@FacilityID int,
@Location nvarchar(500),
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
FirstName nvarchar(50),
LastName nvarchar(50)
)
INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName)
Select Distinct Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName
From Listings
Inner Join Users On Users.UserID = Listings.UserID
Inner Join ListingSpecialtyDetails On ListingSpecialtyDetails.Li
stingID = Listings.ListingID
Where (ListingSpecialtyDetails.S
pecialtyID
IN (@SpecialtyID) OR @SpecialtyID is Null)
