Distinct SQL when many to many
===

3 tables sto_Product, sto_ProductXCategory and sto_Category 

 - sto_Product has all the product info (bvin=Primary Key)
 - sto_Category has all the Categoryinfo (bvin=Primary Key)
 - sto_ProductXCategory (ProductId and CategoryId are PK and FK)

currently it results in duplicate products because some products are duplicate in sto_ProductXCategory (with different CategoryId's).

this SQL statement is part of a larger stored procedure so I'm trying to keep it similar.

This is the statement I'm trying to fix

    with product as  
    (  
		select distinct p.* 
		from sto_Product p   
			inner join sto_ProductXCategory px on(p.bvin = px.productid)  
		where p.parentID = '' 
			and p.Status = 1 
			and p.siteprice >=0 
			and p.siteprice <= 2147483647 
			and ( 
					(p.manufacturerid = '8036ab2c-641e-487b-a577-76c09c5c3cc9'
					)
				) 
	),      
	AllProducts AS 
		(
			SELECT   
				RowNum =       
					CASE        
						WHEN 6 = 0 
							THEN ROW_NUMBER() OVER (ORDER BY px.SortOrder)       
						WHEN 0  = 1 
							THEN ROW_NUMBER() OVER (ORDER BY  px.SortOrder)      
						WHEN 0  = 2 
							THEN ROW_NUMBER() OVER (ORDER BY ProductName)       
						WHEN 0  = 3 
							THEN ROW_NUMBER() OVER (ORDER BY SitePrice)       
						WHEN 0  = 4 
							THEN ROW_NUMBER() OVER (ORDER BY SitePrice DESC)            
						WHEN 6  = 6 
							THEN ROW_NUMBER() OVER (ORDER BY ProductName DESC)       
						ELSE ROW_NUMBER() OVER (ORDER BY px.SortOrder)      
					END,        
				p.* 
			from product p     
				inner join sto_ProductXCategory px on(p.bvin = px.productid) 
		)    
			SELECT AllProducts.RowNum,*, 
				(SELECT COUNT(*) FROM AllProducts) As TotalRowCount  
			FROM AllProducts 
			WHERE RowNum   BETWEEN (0 + 1) and (12 )     
		ORDER BY AllProducts.RowNum  

thanks in advance