Having trouble grouping query output

[PushCode]

I'm doing pretty much the same thing on two different pages. It works as it should on one page and doesn't on the other. The two pages are pulling from different tables but everything else is the same. I'm hoping someone can see what I'm doing wrong here.

I'll explain the working page first... it is querying two tables to get a list of downloadable files, and the goal is to group them into their respective categories.
One table is Downloads, which has the following fields: id, cat_id, title, location, file_name, date_uploaded, downloads

The other table is Download_categories, whith the following fields: cat_id, category

Here's the query:
qDloads.Source = "SELECT a.title, a.location, a.file_name, (select b.category from download_categories b where b.cat_id = a.cat_id) as category FROM downloads a ORDER BY a.title"
And here's the code to output and group the results:

<% While ((Repeat1__numRows <> 0) AND (NOT qDloads.EOF)) %>
			  <% If strCat <> qDloads("category") Then
				strCat = qDloads("category")
				Response.Write "<h4 style='color:#336699;'>" & strCat & "</h4>"
				End If
				strTitle = qDloads("title")
				strLoc = qDloads("location")
				strFile = qDloads("file_name")					
				Response.Write "<li> <a href='" & strLoc & strFile & "'>" & strTitle & "</a><br>"
			  %>	
              <% 
			    Repeat1__index=Repeat1__index+1
			    Repeat1__numRows=Repeat1__numRows-1
			    qDloads.MoveNext()
			  Wend
			  %>

This works great. It outputs each category only once and displays the appropriate files under their appropriate category. You can see it at a free server it is residing on now:

http://home.1asphost.com/pushcode/clients/syv/downloads/default.asp

The second page, the one that doesn't work, outputs all of the results, but doesn't properly group them. It outputs each category more than once and doesn't group the files as it should. You can see this one here:

http://home.1asphost.com/pushcode/clients/syv/news/default.asp

I litterally copied the code from the working downloads page to create this page and just changed the code where appropriate. Here's the setup for this page.

The News table has the following fields: id, date_entered, cat_id, title, description

The News_categories table has the following fields: cat_id, news_cat

Here's the query:
qNews.Source = "SELECT a.id, a.title, a.date_entered, (select b.news_cat from news_categories b where b.cat_id = a.cat_id) as category FROM news a"

And here's the code to output and group the results:

<% While ((Repeat1__numRows <> 0) AND (NOT qNews.EOF)) %>
			  <% If strCat <> qNews("category") Then
				strCat = qNews("category")
				Response.Write "<h4 style='color:#336699;'>" & strCat & "</h4>"
				End If
				strTitle = qNews("title")
				strID = qNews("id")
				strDate = qNews("date_entered")					
				Response.Write "<a href='detail.asp?id=" & strID & "'> <img src='../assets/img/arrow_rgt.gif' width='9' height='5' border='0'></a> <a href='detail.asp?id=" & strID & "'><span class='news_items'>" & strTitle & "</span></a> (" & strDate & ")<br>"
			  %>	
              <% 
			    Repeat1__index=Repeat1__index+1
			    Repeat1__numRows=Repeat1__numRows-1
			    qNews.MoveNext()
			  Wend
			  %>

Can anyone see why this page would not propery group the output? Thanks for any help!

 

[xwb]

Are you missing an "order by" or "group by" in the second query?

 

[PushCode]

Yes I've tried those, with no success. Group by doesn't really make sense for this query, and order by does indeed change around the order, but still doesn't properly group the items into news and events categories.

Anyone see what's going wrong here?

 

[ogri]

Hi

Think you need to change your sql to:-

SELECT a.id, a.title, a.date_entered, (select b.news_cat from news_categories b where b.cat_id = a.cat_id) as category FROM news a order by category

While the first is working that is purely by luck as the order of the titles happens to also be the order of the categories. As such you need to change the first one to:-

SELECT a.title, a.location, a.file_name, (select b.category from download_categories b where b.cat_id = a.cat_id) as category FROM downloads a ORDER BY category, a.title

All the best

Keith

 

[PushCode]

Thanks Keith. That actually didn't work, but I finally figured out what does work. I used inner joins instead of the query in a query method I had been using. IE: SELECT a.id, a.title, a.date_entered, b.news_cat FROM news a INNER JOIN news_categories b ON a.cat_id = b.cat_id ORDER BY b.news_cat

I'm not sure why this works and the other doesn't though. If anyone has any opinions on that, I'd be glad to hear 'em.

Thanks all.

 

[gmmastros]

Since you don't specify the order of the category, I suspect that you've been getting lucky. The query is probably returning the data in the order it was entered. I urge you to change the other query too. This problem is not too uncommon when you are dealing with relatively small amounts of data because the sorting may (or may not) work out ok. Imagine you have a list of 2 items. Without sorting at all, you have a 50/50 chance of the list coming out the way you want it to.

You may also want to add a second field in the order by so that the articles within the categories are also sorted.

[tt]
SELECT a.id,
a.title,
a.date_entered,
b.news_cat
FROM news a
INNER JOIN news_categories b
ON a.cat_id = b.cat_id
ORDER BY b.news_cat[!],
a.title[/!]
[/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PushCode]

Good points George. Thanks.

 

[ogri]

Hi

You definatly need an order clause to force them into the appropriate order.

Only thing I can think of is that the flavour of SQL you are using does not like ordering on a derived column (ie, from the subselect), but the join is a better way of doing things anyway.

All the best

Keith