Hi!

I've got a table SupplierItem with full text catalogue set up on it
and I need to search two columns of it, Description2 and
SupplierItemCode, for any combination of terms entered in any order
with wildcards
(ie if user puts in FOLDER, it finds FOLDERS as well) and also search
for two columns non-FTS for
possible entries as well. I've played around with wildcards, CONTAINS
but it still doesn't work properly!!
Please help...

Here's the example:

        SELECT
            SupplierProduct.SupplierProductId,
            SupplierProduct.SupplierProduct,
            CurrentItem.SupplierItemId,
            CurrentItem.SupplierItemCode,
            CurrentItem.Description2,
            CurrentItem.BuyUnit,
            CurrentItem.PricingUnit,
            CurrentItem.OCCost,
            CurrentItem.OCCost1,
            CurrentItem.OCCost2,
            CurrentItem.OCCost3,
            CurrentItem.ConnCost,
            CurrentItem.ConnCost1,
            CurrentItem.ItemStatusId,
            ItemStatus.ItemStatusName,
            ItemStatus.ItemStatusImage,
            ItemStatus.ItemStatusColour,
            Supplier.SourceCode,
            CurrentItem.UnitId,
            CatalogueItem.Item
        FROM    SupplierItem CurrentItem
            INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
SupplierProduct.SupplierProductId)
            INNER JOIN Supplier ON (Supplier.SupplierId =
SupplierProduct.SupplierId)
            INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
ItemStatus.ItemStatusId)
            INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
CurrentItem.BaseProductId)
            LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
BaseProduct.BaseProductId)
            AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived

        WHERE    (IsApproved = 1 -- item must be approved
        AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
archived
        )
      AND ( (@SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
@SearchTerm))
            OR (@SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
@SearchTerm))
            OR @SearchTerm = CatalogueItem.Item)
            OR (@SearchTerm = BaseProduct.BaseItem)
        )
        AND CurrentItem.OCCost > 0
        ORDER BY
            CurrentItem.SupplierItemId