Thread

Hi all,

I hope that i can ask this here, because i did not found any other forum here where i should post this.

My problem is:
I have two query’s like this:

SELECT *,
(SELECT COUNT(*) 
FROM 
topics 
WHERE 
topics.topic_cat = categories.cat_id) AS aantaltopics 
FROM 
categories

and

SELECT *,
(SELECT COUNT(*) 
FROM 
posts
WHERE 
posts.post_topic = topics.topic_cat ) AS aantalposts 
FROM 
topics
GROUP BY
topics.topic_cat

These query’s gives the right amount from the posts, and topics.
But now i am stuck because they have to be combined in one query.
how can i do this?

thnx for the advise.

Create a pivot table to bridge each table. Or, if you have already FK, use JOIN or LEFT JOIN statement.

Create a pivot table to bridge each table. Or, if you have already FK, use JOIN or LEFT JOIN statement.

Depends on what type of DB tables you are using. If you are using InnoDB you can setup FK relationships just as toopay has suggested. This is probably the way you need to do it in order to achieve the JOIN properly.

I find it hard to believe that these queries are giving you the correct information, since you are storing topics -> categories and posts -> topics on the same exact key.

Your question isn’t really clear, but it seems like you want:

- All post information, with topic and category
- All topic information (including number of posts)
- All category information (including number of categories)

SELECT posts.*, topics.topic_id, topics.topic_name, categories.category_id, categories.category_name
FROM posts
JOIN topics ON posts.topic_id = topics.topic_id
JOIN categories ON topics.category_id = categories.category_id
ORDER BY categories.category_id ASC, topics.topic_id ASC

This will give you every post, it’s topic, and the topic’s category (name and id, if you need both), sorted by category and then topic. This will be significantly easier to work with than trying to write subqueries to sum up the number of posts per topic and topics per category.