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
[quoted text, click to view] > dbo.Date(year(getdate()), 12, 31) ))
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
