Sorting results of one table using two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting results of one table using two tables

Author

Topic

NewMedia42
Starting Member

35 Posts

Posted - 2004-10-06 : 22:35:38

I'm trying to find the simpliest method to do the following. I currently can do it using cursors, but I'd like to know if there are any other alternatives that might lend themselves to clearer/faster performance.

I have two tables:

TableItem contains:
(int) ItemID
(int) CategoryID
(varchar) Name

TableCategory
(int) CategoryID
(varchar) Name

What I want to return is the members of TableItem sorted by name, grouped by CategoryID which is also sorted by name. If I wasn't interested in sorting by the Category name, then I could easily use:

select * from [TableItem] order by [CategoryID],[Name]

but this ignores the Name of the Category, instead basically listing in the order they were inserted in the DB. My cursor implementation is pretty basic as well, I go through TableCategory, then do the above query minus the [CategoryID] filter and adding a WHERE ID=CursorID.

Hopefully that's clear enough to everyone - any help would be greatly appreciated!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-06 : 23:07:20

are you familiar with JOIN's ?

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-06 : 23:35:32

SELECT
ti.ItemID,
ti.CategoryID,
ti.Name
FROM
TableItem ti
INNER JOIN TableCategory tc ON ti.CategoryID = tc.CategoryID
ORDER BY
ti.CategoryID,
ti.Name

--Is this what you are looking for?
--Bad day Jeff? lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources