Forum query problem

[benjaminFowl87]

Hi there I'm having trouble a with a forum query

here are my 3 tables

categories
category_id
category_name
category_description

threads
thread_id
category_id
thread_title
thread_text

posts
post_id
thread_id
post_text

I want a query that selects all of the top level categories and displays them however in that query I am aware that I can generate the number of threads and number of posts each category has so when presented to the end user it will have the following headings

Name, description, # threads, # posts

This is what I have written so far which i thought worked untill i added another category to find that it doesn't

SELECT *,
DATE_FORMAT(last_post_datetime,\'%d-%m-%Y , %h:%i %p\') as category_datetime
FROM categories c
INNER JOIN ( 
	         SELECT t.category_id,count(*) as num_threads 
	         FROM threads t 
                         GROUP BY t.category_id
	         ) as NT ON NT.category_id = c.category_id 

INNER JOIN (
	        SELECT p.thread_id,count(*) as num_posts
	        FROM posts p
	        GROUP BY p.thread_id
	        ) as NP ON NP.thread_id = NT.category_id
ORDER BY last_post_datetime DESC 
LIMIT 5

Any help would be much appreciated

thanks

[smantscheff]

Where does last_post_datetime come from? Is it a field in categories ?
There is at least one error in your SQL: the join clause ON NP.thread_id = NT.category_id cannot be correct. Does the error persist when you fix that? And what are your unexpected results?

[benjaminFowl87]

Hi there, thanks for the reply

your correct, last_post_datetime is another field in categories, I removed all of the fields that I thought would be irrelevant to query when posting here.

Ok, so when I remove this "ON NP.thread_id = NT.category_id" I will get duplicate results.

The result set I want is the following

categories.category_id
categories.category_name
categories.category_description

Then I want two fields to be created

num_threads
num_posts

These two fields can be created using data from my other two tables.

I want all categories to show, not just one

Hope this makes sense

[smantscheff]

Have a look at this example.

drop table c;
drop table t;
drop table p;

create table p( id integer, id_t integer, content text );
create table t( id integer, id_c integer, content text );
create table c( id integer, name text );

insert into p values (1,1,'c1'),(2,1,'c2'),(3,2,'c3'),(4,2,'c4'),(5,2,'c5');
insert into t values (1,1,'t1'),(2,2,'t2'),(3,2,'t3');
insert into c values (1,'cat1'),(2,'cat2');

select * from
(select c.*, count(*) as count_threads
from c join t on t.id_c=c.id
group by c.id) join1
join
(select c.*, count(*) as count_posts
from c join t on t.id_c=c.id join p on p.id_t=t.id
group by t.id) join2
on join1.id = join2.id

IMHO it does what you require.

+------+------+---------------+------+------+-------------+
| id   | name | count_threads | id   | name | count_posts |
+------+------+---------------+------+------+-------------+
|    1 | cat1 |             1 |    1 | cat1 |           2 |
|    2 | cat2 |             2 |    2 | cat2 |           3 |
+------+------+---------------+------+------+-------------+

[benjaminFowl87]

Thank you very much, that works perfectly.

Before you gave me the solution I was working on something that looked like this, it's an outer join.

SELECT *,	
(SELECT count(*) FROM threads t WHERE t.category_id = c.category_id) as num_threads
FROM categories c
ORDER BY category_order ASC 
LIMIT '.$limit.'
OFFSET '.$offset

Thanks alot, much appreciated