Hi,
I'm having some difficulties as to which approach is the right one in
this scenario:

I have a table of books (tblShopItem), a table of categories (authors,
series etc. = tblShopCategory) and a table that connects these
(tblShop_Item_Category_Ref), so one book can have multiple authors,
series etc.
When I recieve the data, I recieve the list of categories (and
category types) for one book via a UDF as a comma-delimited list, so I
only get one line per book in the resultset.
My problem is that I can't use FREETEXT or FREETEXTTABLE properly in
this context, because I want to search both the Book-table and the
Category-table, so I find books that matches one or the other instead
of books that matches the criteria in BOTH tables!
Also, the @SearchString paramter should be optional which I don't know
how to do when using JOINs. I've tried to create a view of the SELECT-
statement, but are not allowed to create a unique index because of the
UDF - But maybe my approach is wrong - any suggestions would be
appreciated!
Best,
Mazzo

Here's the code:

ALTER PROCEDURE [dbo].[usp_SearchAll] (
@PageIndex int,
@PageSize int,
@SearchString nvarchar(255) = NULL,
@CategoryID int = NULL
)
AS
SET NOCOUNT ON;

WITH SearchTable AS
(
SELECT ROW_NUMBER() OVER (ORDER BY si.ItemName) AS ROW,
si.ID, si.tblShopProductType_ID, si.ItemName, si.ShortDescription,
si.Issue, si.Price, si.CampaignPrice, si.CampaignStartDate,
si.CampaignEndDate, sir.Rating, dbo.GetCategories(si.id) AS Categories
FROM tblShopItem si

JOIN tblShopItem_Category_Ref scr ON scr.tblShopItem_ID = si.ID
LEFT JOIN tblShopItemReview sir ON sir.tblShopItem_ID = si.ID

JOIN FREETEXTTABLE(tblShopCategory, *, @SearchString) AS tblKeyShopCat
ON scr.tblShopCategory_ID = tblKeyShopCat.[KEY]
--JOIN FREETEXTTABLE(tblShopItem, *, @SearchString) AS tblKeyShopItem
ON si.ID = tblKeyShopItem.[KEY]

WHERE scr.tblShopCategory_ID = COALESCE(@CategoryID,
scr.tblShopCategory_ID)
AND GetDate() BETWEEN si.StartDate AND si.EndDate
)
SELECT SearchTable.*, (SELECT MAX(ROW) FROM SearchTable) AS RC FROM
SearchTable
WHERE ROW BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex *
@PageSize

SET NOCOUNT OFF