CODEWITHSUNDEEP
mysql
seoppc
seoppc

Reputation: 2824

mysql mixing counts from several tables

I am trying to achieve total number of topics, total number of posts, and last post for given section, please find db structures and query as following...

fcats

| id | title  | section |
+----+--------+---------+
|  1 | test   | gd      |
+----+--------+---------+

ftopics

| id | title  | cat_id  |
+----+--------+---------+
|  1 | test1  | 1       |
+----+--------+---------+

fposts

| id | post  | topic_id | posted_by
+----+-------+----------+---------+
|  1 | post  | 1        | user    |
+----+-------+----------+---------+

current query

SELECT id, 
       title ,
       (SELECT count(id) 
       FROM ftopics 
       WHERE cat_id = id) AS total_topics 
FROM   fcats 
WHERE  section = "gd"

by using above query, i could only get total_topics for given section, but i am confused about how to get total number of posts, and last post for given section. please help, thanks.

Upvotes: 0

Views: 73

Answers (3)

dani herrera
dani herrera

Reputation: 51705

For first part of your answer you should use count distinct, and for second part a subquery:

SELECT c.id, 
       c.title ,
       count( distinct t.cat_id) AS total_topics ,
       count( distinct p.id) AS total_posts ,
       (select p2.id 
        from ne_forum_posts p2
        inner join ne_forum_topics t2 on p2.topic_id = t2.id
        inner join ne_forum_sub_cats c2 on c2.id = t2.cat_id
        where c2.id = c.id
        order by p2.id desc 
        limit 1) as last_post_id    
FROM   ne_forum_sub_cats c LEFT OUTER JOIN
       ne_forum_topics t on  c.id = t.cat_id LEFT OUTER JOIN
       ne_forum_posts p on p.topic_id = t.id
WHERE  section = "gd"

all typos fixed and tested.

Upvotes: 1

Baaju
Baaju

Reputation: 2020

SELECT  A.id section_id, 
        IFNULL(COUNT(DISTINCT B.id),0) topics_count, 
        IFNULL(COUNT(C.id),0) post_count, 
        (SELECT post from fposts where id = MAX(C.id)) last_post
FROM    fsections A LEFT JOIN ftopics B    
ON      A.id = B.cat_id    
LEFT    JOIN fposts C    
ON      C.topic_id = B.id    
WHERE   A.section = "gd"
GROUP   BY A.id

Also include the null case if the section doesnot have any post

Upvotes: 1

Arion
Arion

Reputation: 31249

Maybe something like this:

SELECT 
    id, 
    title ,
    (
        SELECT 
            count(ftopics.id) 
        FROM 
            ftopics 
        WHERE 
            ftopics.cat_id = fcats.id
    ) AS total_topics,
    (
        SELECT
            COUNT(distinct fposts.id)
        FROM
            ftopics
            JOIN fposts
                ON ftopics.id=fposts.topic_id
        WHERE 
            ftopics.cat_id = fcats.id
    ),
    (
       select 
         fposts.id 
       from fposts
       inner join ftopics on fposts.topic_id = ftopics.id
       inner join fcats c2 on c2.id = ftopics.cat_id
       where fcats.id = c2.id
       order by fposts.id desc 
       limit 1
    ) as last_post_id 
FROM   fcats 
WHERE  section = "gd"

Upvotes: 1

Related Questions