sql statement question

[prichardson]

What is the best way to put this scenario into a SQL statement.I want to retrieve the specific from the Blog_01_Category table (which displays the category options) and I want to GROUP all the posts that are in that particular category from the Blog_01 table.I want to add a 'filter' into the equation, so when the user clicks on the hyperlink text category- it will direct the user to all the posts on that category. In a nutshell, if the category list has 10 categories, with each category having many blog posts to it, when the user clicks on a particular category then all the posts on that category to appear on the page. The equation involves 2 tables- one which has the category options (to display the options on the page) and the second table has the posts which has a column that states which category (to Group all these posts to the selected option)This is what i got so far, can someone please tell me what i should add to get the sql statement to work.

rsBlog_01_NavCategories.Source = "SELECT DISTINCT Blog_01CategoryID, Blog_01_Category FROM weblog_Blog_01_Category"

this is the asp code for the link i have on the page:

<% While ((Repeat1__numRows <> 0) AND (NOT rsBlog_01_NavCategories.EOF)) %><tr><td class="textsmall" style="padding:1px"><a class="subnavlevel3" href="<%=strURLPath%>pages/blog_01/blog_01.asp?Blog_01CategoryID=<%= rsBlog_01_NavCategories("Blog_01CategoryID") %>"><%=(rsBlog_01_NavCategories.Fields.Item("Blog_01_Category").Value)%></a></td></tr><% Repeat1__index=Repeat1__index+1Repeat1__numRows=Repeat1__numRows-1rsBlog_01_NavCategories.MoveNext()Wend%>

[jesh]

If your Blog_01 table has a Blog_01CategoryID like the Blog_01_Category table does, you can get all the entries that are of a particular category (e.g. a category with an ID of 10) like this:

SELECT * FROM Blog_01 WHERE Blog_01CategoryID = 10

[prichardson]

Hi Jesh,Blog_01 table has blog_01ID and has 'Blog_01_Category'and Blog_01_Category table has Blog_01CategoryID and has 'Blog_01_Category'However the List is dynamic and therefore the categories will change, so I think that the sql statement will have to be a little more complex to solve the answer.Jesh- how would you put an sql statement with the above details in the equation?Thanks,Peter

[jesh]

I don't know how you have your data set up, but typically the table structures in the database would look a bit like this:

BlogCategories------------------	CategoryID	CategoryTitle	CategoryDescriptionBlogEntries-----------------	EntryID	CategoryID	EntryText	Date

The CategoryID in the BlogEntries table would match the CategoryID for one of the entries in the BlogCategories table.When a new entry was made for the blog, you'd select a category from your dropdown menu. The dropdown menu would have the "CategoryTitle" as the display text and the "CategoryID" as the value. When the entry was saved in the database, the CategoryID that matched the category that was chosen would be saved in that row in the BlogEntries table. This way, when you wanted to see all the entries that were made in that category, you'd just look for those entries with that particular CategoryID (like that query that I posted).

[prichardson]

Hi Jesh,My tables:Blog_01 table> Blog_01ID > Blog_01_Categoryetc. etc.Blog_01_Category table >Blog_01CategoryID >Blog_01_CategoryThe thing is that the options is not done from a drop down, it is displayed as text on the navigation bar as dynamic asp code that I have put up on the first post.There is more than one variable here...> To display the categories on the navigation bar > when category on navigation bar is clicked then "all the posts from that particular category" to appear in the main body of page.There is 2 places of what happens- 1 is the naviagtion but the second bit is getting all the posts to appear in the center of the page with all the posts.Therefore I am assuming that in the SQL statement i need to write something that combines both elements. Thanks

[jesh]

So, just so I have this straight, the data in your tables looks something like this?

Blog_01	Blog_01ID	Blog_01_Category-----------------------------------------	  4			   Programming	  5			   Programming	  6			   Travel	  7			   EducationBlog_01_Category	Blog_01_CategoryID	Blog_01_Category-------------------------------------------	  1							 Programming	  2							 Travel	  3							 Education

If that's the case, your SQL query would have to look more like:

SELECT * FROM Blog_01 WHERE Blog_01_Category LIKE 'Programming'

On the other hand, if your Blog_01 table looked like this:

Blog_01	Blog_01ID	Blog_01_CategoryID-----------------------------------------	  4			   1	  5			   1	  6			   2	  7			   3

Then, to get all of the entries that were in "Programming" you would do this:

SELECT * FROM Blog_01 WHERE Blog_01_CategoryID = 1

If you also wanted to get the category information you could use an INNER JOIN to the category table on that category ID:

SELECT Blog_01.*, Blog_01_Category.Blog_01_CategoryFROM Blog_01	INNER JOIN Blog_01_Category ON Blog_01.Blog_01_CategoryID = Blog_01_Category.Blog_01_CategoryIDWHERE Blog_01.Blog_01_CategoryID = 1

There will eventually be two queries: One query to get the categories to display in your navigation and another query to display all the posts in a particular category.

[prichardson]

Hi Jesh,This is correct layout:

So, just so I have this straight, the data in your tables looks something like this?

Blog_01	Blog_01ID	Blog_01_Category-----------------------------------------	  4			   Programming	  5			   Programming	  6			   Travel	  7			   EducationBlog_01_Category	Blog_01_CategoryID	Blog_01_Category-------------------------------------------	  1							 Programming	  2							 Travel	  3							 Education

If that's the case, your SQL query would have to look more like:

SELECT * FROM Blog_01 WHERE Blog_01_Category LIKE 'Programming'

However the category will not be fixed, which means that the categorys will change (because you can add/delete the categories in the admin area). Therefore I think the 'like' is making it static to 'programming' so...How can i adapt the sql statement you have said so that it is more dynamic. Would an If statement in the recordset or asp code to make it dynamic?Thanks,

[prichardson]

This is what i have got so far- which will show all the cricket for example, but i have to 10 categories and they could change on a frequent basis, so how can i get make it dynamic?

"SELECT Blog_01_Title, Blog_01_Intro FROM weblog_Blog_01 INNER JOIN weblog_Blog_01_Category ON weblog_Blog_01.Blog_01_Category = weblog_Blog_01_Category.Blog_01_Category WHERE weblog_Blog_01_Category.Blog_01_Category = 'cricket'"

Any suggestions or ideas anyone?