Ok, I have a e-commerce store that uses stored procedures to sort products. I have figured out how to sort my name and price but am having trouble sorting by rating bc the rating table doesnt have all the productids (only ones that have a rating) and also the rating table is in a different table than the products table so when I use the code below it only shows products that have a rating. Heres an example http://www.tunerplaza.com/c-304-carburetion.aspx?


Heres the sql code For the rating....

ELSE IF @sortEntityName = 5 BEGIN --Sort By Rating

        INSERT #RowNumber (rating) 
        SELECT RT.Productid 
        FROM Rating RT 
        GROUP BY RT.ProductID
        ORDER BY MIN(RT.Rating)

        INSERT #displayorder select rating, displayorder from #RowNumber
END

I tried the link ..
Sorting feature is working fine ..

:-/

I think you want something like this

Select distinct pt.ProductID
from Products pt
left join Rating RT
on pt.productid = rt.productid
order by IsNull(Min(rt.Rating),0)

ignore that last one and try this instead


Select pt.ProductID
from Products pt
left join Rating RT
on pt.productid = rt.productid
group by pt.productid
order by IsNull(Min(rt.Rating),0)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.