0

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";
4
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by d5e5
0

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.

Edited by lifeworks: addition of another link

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.