MySql Syntax question

The following syntax returns records no problem.  The problem is that the comment counts story comments that haven't been approved so the count is off.  In the notes table there is a field called note_status_id the value 2 means it hasn't been approved.  So trying to figure out with the syntax to include the notes count that only have been approved.

select IFNULL(COUNT(notes.fk_story_id), 0) as commentcnt, 
storys.story_id as storyid, storys.story, story_users.first_name, 
story_users.last_name, storys.title from storys 
inner join story_users on storys.fk_user_id = 
story_users.user_id left join notes on 
storys.story_id = notes.fk_story_id 
where fk_status_id = 2 group by storys.story_id order by storyid desc

Open in new window

stargateatlantisAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
You have two options: either exclude these notes from the selection completely:
select IFNULL(COUNT(notes.fk_story_id), 0) as commentcnt, 
storys.story_id as storyid, storys.story, story_users.first_name, 
story_users.last_name, storys.title 
from storys 
inner join story_users on storys.fk_user_id = 
story_users.user_id left join notes on 
storys.story_id = notes.fk_story_id 
where fk_status_id = 2 and notes.note_status_id <> 2
group by storys.story_id 
order by storyid desc

Open in new window

or do not count them:
select IFNULL(COUNT(CASE WHEN notes.note_status_id <> 2 THEN notes.fk_story_id END), 0) as commentcnt, 
storys.story_id as storyid, storys.story, story_users.first_name, 
story_users.last_name, storys.title 
from storys 
inner join story_users on storys.fk_user_id = 
story_users.user_id left join notes on 
storys.story_id = notes.fk_story_id 
where fk_status_id = 2 
group by storys.story_id 
order by storyid desc

Open in new window

0
 
stargateatlantisAuthor Commented:
So basically do not count them when the status is 2 correct
0
 
chaauCommented:
Yes. have you tried the queries? Do they produce the desired result?
0
All Courses

From novice to tech pro — start learning today.