|
|
Posted by Erland Sommarskog on 11/23/06 22:47
Nobody (nobody@cox.net) writes:
> 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.
No, you don't need DISTINCT. You need to learn to use EXISTS:
SELECT C.Name
FROM tblCategories C
WHERE EXISTS (SELECT *
FROM tblItems I
WHERE I.CategoryID = C.CategoryID
AND I.BrandID = @brandid)
ORDER BY C.DisplayOrder
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|