0

I'm trying to do a complicated SQL, where I write all categories, how many threads are in the category on the page. Now i'm also trying to make it, so my Repeater, shows the latest thread in the category, based on Thread Datetime.

This is my SQL:

SELECT categories.category_name, categories.category_id, 
COUNT(threads.thread_category_id) 
AS 'threadCount' FROM threads 
RIGHT OUTER JOIN categories 
ON categories.category_id = threads.thread_category_id
GROUP BY categories.category_name, categories.category_id

I've been trying different ways, and also asked my teacher. We can't seem to find a way to make this possible, into one SQL Query, to run in a Repeater.

Basiclly: Repeater that contains every Category with a

Category name,
Category ID,
Count of threads in Category,
Latest Thread title,
Latest Thread datetime.

Thread title DB field: threads.thread_title

Thread datetime DB field:threads.thread_datetime

Would appreciate any idea or help on this!

Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
Kevin Jensen Petersen
  • 423
  • 2
  • 22
  • 43
  • How to execute the query ? ADO ? Entity Framework ? Try to transform the query into View and select from this. – Max Mar 13 '13 at 09:41
  • I am running this Query, though Query builder right now, if that's your question? – Kevin Jensen Petersen Mar 13 '13 at 09:43
  • 1
    I would start by converting it into a plain LEFT JOIN, flipping {threads, categories}. And using correlation names / aliases. – wildplasser Mar 13 '13 at 09:55
  • [This question](http://stackoverflow.com/questions/1305056/selecting-all-corresponding-fields-using-max-and-group-by?rq=1) should help on selecting title of latest thread. The rest is a matter of implementation. – default locale Mar 13 '13 at 09:58
  • Do you still need the count, or only the {title,datetime} of the most recent thread? What should be the result if there are not hreads for a category? – wildplasser Mar 13 '13 at 10:13

0 Answers0