I have 3 tables
tbpProducts
tblCategories
tblProductCategories (allocation)
tblProductCategories acts as an allocation table allowing there to be multiple categories per product.
I have a problem with a complexish (for me anyway) query I have which returns a resultset back for a query. Its made more complex by the fact that it deals with pagin result sets too.
The problem is that I need to return DISTINCT products however the DISTINCT keyword does not work for products which are in multiple categories as the rows are obviously not unique.
How in the following query do I return rows where KEYPRODUCT is unique?
Thanks very much
CREATE PROCEDURE ts_GetAllToysByWhereClause
@CurrentPage int,
@PageSize int,
@WHEREClause nvarchar(1000)
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY NOT NULL PRIMARY KEY,
KeyProduct int,
KeyFCategory int,
ProductUrl nvarchar(80),
Code nvarchar(80),
ProductNameShort nvarchar(80),
ProductDescShort nvarchar(300),
MetaKeywords nvarchar(200),
MetaDescription nvarchar(500),
PageTitle nvarchar(80),
ImageName nvarchar(80),
ImageAltTag nvarchar(80),
LinkTitleTag nvarchar(80),
KeyFGroup int,
SortPriority int,
Price money,
KeyFGender int,
AgeMin int,
AgeMax int,
IsOutOfStock bit,
IsLowStock bit,
IsHide bit,
IsNoGiftWrap bit,
IsInternetOnly bit,
IsTopTen bit
)
-- Create a variable @SQLStatement
DECLARE @SQLStatement nvarchar(1000)
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SET @SQLStatement = "INSERT INTO #TempTable SELECT DISTINCT KeyFCategory, KeyProduct, ProductUrl, Code, ProductNameShort, ProductDescShort, MetaKeywords, MetaDescription, PageTitle, ImageName, ImageAltTag, LinkTitleTag, KeyFGroup, SortPriority, Price, KeyFGender, AgeMin, AgeMax, IsOutOfStock, IsLowStock, IsHide, IsNoGiftWrap, IsInternetOnly, IsTopTen FROM tblProductCategories INNER JOIN tblProducts ON KeyFProduct = KeyProduct" + @WHERECLAUSE + " ORDER BY SortPriority"
-- Execute SQL statement, inserting into the #TempTable
EXEC(@SQLStatement)
--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT KeyFCategory, KeyProduct, ProductUrl, Code, ProductNameShort, ProductDescShort, MetaKeywords, MetaDescription, PageTitle, ImageName, ImageAltTag, LinkTitleTag, KeyFGroup, SortPriority, Price, KeyFGender, AgeMin, AgeMax, IsOutOfStock, IsLowStock, IsHide, IsNoGiftWrap, IsInternetOnly, IsTopTen
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRec
GO