MySQL Query COUNT Output

Question

I am trying to get information from another SELECT inside of the same query, however I am not too sure how to get the required field I am after.

SELECT t.`id`, t.`title`, t.`author`, t.`content`, ctitle, cid, comments

FROM `tutorials` AS `t`, 

  (
    SELECT tc.`id` as `cid`, tc.`title` as `ctitle` 
    FROM `tutorial_categories` AS `tc` 
    WHERE `title` LIKE '%category title'
  ) AS `c`,

  (
    SELECT COUNT(com.`id`) as `comments`
    FROM `tutorial_comments` AS `com`
    WHERE `tutorial_id` = c.cid
  ) as `comments`

WHERE t.`category` = c.`cid` AND t.`status` = '1'
ORDER BY `id` ASC

I am trying to fetch the id from tutorial_categories and use it in tutorial_comments. All I am trying to do as the final output is get the count of how many comments are listed for each tutorial.

Cheers,

Jacob

Derrick Zhang · Accepted Answer · 2011-12-08 03:21:57Z

2

Jacob

You need add a group by clause like this

select t.id, t.tilte, t.author, t.content, count(com.id) as comments
from tutorials as t
   join tutotials_categories as cat
      on t.category = cat.id
   join tutorials_comments as com
      on com.tutorial_id = t.id
where cat.title like'%category title'
   and t.status = 1
group by com.id
order by t.id asc

I used the ansi join form

answered Dec 8, 2011 at 3:21

Derrick Zhang

2824 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacob Talbot Over a year ago

now it's only displaying results that have comments, rather than all the results per the category.

Derrick Zhang Over a year ago

I'm sorry but I didn't catch what you mean. You can try left join to display those tutorials that don't have comments, and you can order by category to sort the result set. btw, I think you can try @erwin 's script.

Erwin Brandstetter · Accepted Answer · 2011-12-08 03:22:29Z

This should clean up your query:

SELECT t.id, t.title, t.author, t.content, c.ctitle, c.cid, com.comments
FROM   tutorials AS t
LEFT   JOIN (
    SELECT tutorial_id, COUNT(com.id) as comments
    FROM   tutorial_comments AS com
    GROUP  BY 1
    )  AS com ON com.tutorial_id = t.category
LEFT   JOIN (
    SELECT tc.id as cid, tc.title as ctitle 
    FROM   tutorial_categories AS tc 
    WHERE  title LIKE '%category title'
    )  AS c ON t.category = c.cid
WHERE  t.status = '1'
ORDER  BY t.id

The LEFT JOIN prevents that tutorials disappear that don't find a match.
I made that explicit JOINs with JOIN conditions, that is easier to understand and also the proper way.
Your cardinal problem was that you had the join condition for the counted comments inside the brackets instead of outside, which cannot work that way.

BizApps · Accepted Answer · 2011-12-08 03:19:53Z

0

Can you try this one:

SELECT t.`id`, t.`title`, t.`author`, t.`content`, c.title, c.cid, ct.comments
FROM `tutorials` AS `t`

LEFT OUTER JOIN (
    SELECT tc.`id` as `cid`, tc.`title` as `ctitle` 
    FROM `tutorial_categories` AS `tc` 
    WHERE `title` LIKE '%category title'
  ) AS `c` ON t.`category` = c.`cid` 

LEFT OUTER JOIN (
    SELECT COUNT(com.`id`) as `comments`
    FROM `tutorial_comments` AS `com`
    group by com.`id`
  ) as `ct` on  ct.`tutorial_id` = c.cid

WHERE t.`status` = '1'
ORDER BY `id` ASC

answered Dec 8, 2011 at 3:19

BizApps

6,1409 gold badges43 silver badges65 bronze badges

Collectives™ on Stack Overflow

MySQL Query COUNT Output

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related