Pls Help With JOIN query...

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.Just remove the order by ie:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */

Nobody wrote:

Quote:

Originally Posted by

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.

|||Okay my mistake. This will do the job:

select a.tblCategories.Name
from (select DISTINCT top 100 percent tblCategories.Name,
tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
tblCategories.Name,tblCategories.DisplayOrder) a

Unlike other databases, SQL Server does not allow 'order by' within
derived tables, so had to use top etc...

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Just remove the order by ie:
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */
>
Nobody wrote:

Quote:

Originally Posted by

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.

|||Nobody (nobody@.cox.net) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 22 Nov 2006 16:59:17 -0800, Nobody wrote:

Quote:

Originally Posted by

>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|||On 23 Nov 2006 01:48:32 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>Okay my mistake. This will do the job:
>
>select a.tblCategories.Name
>from (select DISTINCT top 100 percent tblCategories.Name,
>tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
>tblCategories.Name,tblCategories.DisplayOrder) a
>
>Unlike other databases, SQL Server does not allow 'order by' within
>derived tables, so had to use top etc...

Hi othellomy,

Though you can use ORDER BY in a subquery if you also use TOP, the ORDER
BY will only be used to determins which rows meat the TOP criterium;
there is no guarantee that the actual order of the query will be the
same. In fact, SQL Server 2005 will ignore both TOP 100 PERCENT and the
accomanying ORDER BY, since it is essentially a no-op to restrict the
output to 100 percent of the regular output.

If you really want to move the DISTINCT to a subquery (which in this
case is NOT needed - see my reply to Nobody), you could use

SELECT a.Name
FROM (SELECT DISTINCT c.Name, c.DisplayOrder
FROM Categories AS c
INNER JOIN Items AS i
ON i.CategoriID = c.CategoryID
WHERE i.BrandID = 1) AS a
ORDER BY a.DisplayOrder;

(untested)

--
Hugo Kornelis, SQL Server MVP