where clause limiting response rows

[lifeworks]

Hi

Im trying to pull out a list of meetings, and the associated number of comments attached to each meeting. Comments are assigned a category (ie meetings) and a linkid (the uniqe id of the meeting) - Im using this query, buts its cutting out all meetings that don't have any comments attached, and Ive hit a mental block... how do I pull out all meetings (and a value of zero if there are no comments?!)

$sql = "SELECT m.id,m.title, m.blurb, m.mktime, m.start, m.stop, u.name AS first, u.surname, v.name AS venue, COUNT(c.id) as comment_count FROM meetings AS m 
LEFT JOIN users As u ON u.id = m.userid 
LEFT JOIN venues AS v ON v.id = m.venueid 
LEFT JOIN comments AS c ON c.linkid = m.id 
WHERE c.category = 'meetings' 
ORDER BY m.mktime ASC";

[Oxiegen]

When you use LEFT JOIN, only those records from that table that matches are included and therefore counted.
Perhaps this could be useful: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html

[drjohn]

Make your last join, the one to the comments table, a LEFT OUTER JOIN

[lifeworks]

hi, thanks for the advice on the 'left outer join', unfortunately hasnt solved the problem... i googled left-out-joins and the example I saw will give me a record of each table1 item, without table2 attachments... but I need a join which gives me all of table 1, and table 2 attachments if they exist...

link to example http://www.liamdelahunty.com/tips/mysql_left_outer_join.php

I take that back, just found another article which backs up the 'left-outer-join' theory...
http://forums.mysql.com/read.php?10,153142,154537

Ill keep trying, maybe its something else in the query.

[d5e5]

Hi

Im trying to pull out a list of meetings, and the associated number of comments attached to each meeting. Comments are assigned a category (ie meetings) and a linkid (the uniqe id of the meeting) - Im using this query, buts its cutting out all meetings that don't have any comments attached, and Ive hit a mental block... how do I pull out all meetings (and a value of zero if there are no comments?!)

$sql = "SELECT m.id,m.title, m.blurb, m.mktime, m.start, m.stop, u.name AS first, u.surname, v.name AS venue, COUNT(c.id) as comment_count FROM meetings AS m 
LEFT JOIN users As u ON u.id = m.userid 
LEFT JOIN venues AS v ON v.id = m.venueid 
LEFT JOIN comments AS c ON c.linkid = m.id 
WHERE c.category = 'meetings' 
ORDER BY m.mktime ASC";

To pull out all meetings including those with no comments you need to consider that sometimes the values in the result set from the comments table will be null (for those meetings that have no corresponding rows in the comments table.) Try altering your WHERE clause as follows: WHERE c.category = 'meetings' OR c.category IS NULL