is this query possible - SQL Server Forums

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)

is this query possible

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-04 : 17:13:01

The procedure below "SELECT_FORUM" gets all the forums for the specific category passed to it. The query for getting the categories is

SELECT cat_ID, cat_Name FROM forum_category ORDER BY cat_Order ASC

Is it Possible to have 1 SP that grabs all the forums, for each category that is found? Rather then having a looping recordset like I have now.

THanks alot,
MIke

CREATE PROCEDURE select_forum
(
@cat_ID tinyint
)
AS
SET NOCOUNT ON
SELECT forum_ID, f_Subject, f_Description, f_Topics, f_Count FROM forum_forum WHERE cat_id=@cat_ID ORDER BY f_Order ASC

GO

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-04 : 18:13:29

Mike,

Isn't this just an inner join?

 
SELECT FF.* FROM forum_forum AS FF
INNER JOIN forum_category AS CF ON CF.cat_ID = FF.cat_ID

Or am I missing something?

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-04 : 23:11:12

thanks, joins seem to confuse me. I have to bring this question 1 step further if you don't mind.

how can I get that query to be sorted by first CAT_ID ASC, and then sorted by f_Order ASC

so it will bring back all the cat_ID's with 1 first and then sort them according to their respective f_order.

Thanks alot!

Mike

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-04 : 23:19:54

SELECT FF.* FROM forum_forum AS FF
INNER JOIN forum_category AS CF ON CF.cat_ID = FF.cat_ID
ORDER BY CF.CAT_ID, FF.Forder

?? Or am I missing something too?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-06-04 : 23:32:22

perfect, thanks guys I didnt know you could do an order by clause like that

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-04 : 23:38:34

quote:

perfect, thanks guys I didnt know you could do an order by clause like that

neither did I until someone showed me how!....mind you that was back at uni...but nonetheless. hmmmmm byrmol is very quiet!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-05 : 00:07:42

Can't talk.. I'm in the code zone....

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources