Page 1 of 1

Query Problem

Posted: Tue Jun 16, 2009 5:43 am
by waqas_punjabian
Hi all,

I need a query that should show total number of records of children and grand children tables against a parent entry.

For example,
I have 4 tables,

1) Building (parent)
2) Category (child of building)
3) Discussion (child of Category / grand child of Building)
4) Post (child of Discussion / grand child of Category / grand grand child of Building)

Now I need a query which should state all records of a building table. Additionally, it should show total number of Categories, Discussions and Posts, against each building.

Can you please guide me how can I do that?

I have tried following query, but it counts only for the first child entry. For example, I have 6 Discussions in 2 categories (5+1). Where as it only shows 5 records against a building.

Code: Select all

 
SELECT
    building.id,
    building.address,
    @categories:=(SELECT Count(category.id) FROM category WHERE building.id = category.building_id) AS categories,
    @discussions:=(SELECT Count(discussion.id) FROM discussion WHERE discussion.category_id = category.id) AS discussions,
    @posts:=(SELECT Count(post.id) FROM post WHERE post.discussion_id = discussion.id) AS posts,
    @users:=(SELECT Count(user_building.id) FROM user_building WHERE building.id = user_building.building_id) AS users
FROM
    building
    Left Outer Join category ON building.id = category.building_id
    Left Outer Join discussion ON category.id = discussion.category_id
    Left Outer Join post ON discussion.id = post.discussion_id
    Left Outer Join user_building ON building.id = user_building.building_id
GROUP BY
    building.id
 
Let me know if you need SQL script of tables?

regards,
Waqas