|
|
Posted by Hugo Kornelis on 11/23/06 23:14
On Wed, 22 Nov 2006 16:59:17 -0800, Nobody wrote:
>I'm trying to write a stored proc...
>
>Basically, I have a tblItems table which contains a list of every item
>available. One of the columns in this table is the brand... for test
>purposes, I hardcoded the BrandID=1...
>
>tblItems also contains a category column (int) which contains a categoryID
>of 0..3...
>
>I then have a category table which has CategoryID, Name, and DisplayOrder...
>
>So basically what I'm trying to do is return a list of Category NAMES that
>have items in them for a specifc brand... but I want to sort the returned
>categories by the DisplayOrder column...
>
>this is what I have now:
>
>
>select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
>tblCategories
>INNER JOIN tblItems
>on tblCategories.CategoryID = tblItems.CategoryID
>where BrandID=1 order by tblCategories.DisplayOrder
>
>this does what I want it to do, but its returning TWO columns... Name AND
>DisplayOrder... I only want to return Name, but if I take the DisplayOrder
>out of the select portion, it errors out because it can't order by that...
>
>Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
>the same category name.
>
Hi Nobody,
Since you don't display any columns from tblItems, the only reason to
use it in this query is obviously to check for existance of at least one
row with BrandID equal to 1. That means that you can rewrite your query
as
SELECT c.Name --, c.DisplayOrder
FROM Categories AS c
WHERE EXISTS
(SELECT *
FROM Items AS i
WHERE i.CategoryID = c.CategoryID
AND i.BrandID = 1)
ORDER BY c.DisplayOrder;
You'll probably see a performance increase as well.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|