sql - Resulting data on last thread_date

Question

As I fixed the previous question thanks to the answer, I am sticking now in the part to retrieve a thread based on the last thread_date.

The code itself seems to be working fine, but it is only printing one result out instead of others.

The thread has a threads.cat_id which is linked to thesubsubcategory.extra_cat_id.

SELECT 
parent.subcat_id, 
parent.subcat_name, 
child.subsubcat_name, 
child.subcat_id, 
child.cat_id, 
kid.thread_name, 
kid.cat_id, 
kid.thread_date 
FROM 
subcategories parent  
JOIN subsubcategories child 
ON child.cat_id = parent.cat_id
 JOIN threads kid ON child.extra_cat_id = kid.cat_id 
WHERE thread_date = (SELECT MAX(thread_date) FROM threads)

What I am expecting is this:

 Category
    Subcategory       Latest thread
    Subcategory       Latest thread

What I am getting is this:

Category
  Subcategory       Latest thread  

SQL fiddle: http://sqlfiddle.com/#!9/52e27/2

Any solutions to it?

Thanks!

Answer 1

If my guess is right, you want to edit the where clause to show more based on last date as your threaddate is a datetime, you have to convert it to a date format before comparing it.

    SELECT parent.subcat_id,
        parent.subcat_name,
        child.subsubcat_name,
        child.subcat_id,
        child.cat_id,
        kid.thread_name,
        kid.cat_id,
        kid.thread_date
    FROM subcategories parent
    INNER JOIN subsubcategories child
        ON child.cat_id = parent.cat_id
    INNER JOIN threads kid
        ON child.extra_cat_id = kid.cat_id
WHERE convert(date,thread_date,108) = (
        SELECT MAX(convert(date,thread_date,108))
        FROM threads
        )