Database relationships

I'm trying to display all the boards in a certain category, but I'm having trouble with the SQL query. I want it to go through all the posts in a certain category, take in the user id from that, find the username from the users table, and then count how many comments there are in the post.

Here's the tables and some of the fields:

boards  
    board_id  

comments - the replies to the post  
    comment_id  

discussion - the posts
    discussion_id  
    discussion_user  
    discussion_board  
    discussion开发者_Python百科_time  
    discussion_title  

users  
  id  
  username      

Originally I had this:

SELECT 
    a.discussion_id, 
    a.discussion_time, 
    a.discussion_title, 
    a.discussion_type, 
    a.discussion_media, 
    b.username, 
    Count(c.comment_id) AS totalComments
FROM 
    discussion a, 
    users b, 
    comments c
WHERE 
    discussion_board='".$board['board_id']."' AND 
    b.id=a.discussion_user AND 
    c.comment_post=a.discussion_id  

But it only shows post if it can find comments.

How can I fix this? I'm still learning more about SQL and database relationships. Left joins?

---

Left joins are the way to go, since they will pull everything from a, regardless of whether there's a corresponding entry in b or c. More on Joins (and SQL in general) can be found here.

---

SELECT a.discussion_id, a.discussion_time, a.discussion_title, a.discussion_type, a.discussion_media, b.username, Count(c.comment_id) AS totalComments FROM discussion a,
left join  users b on b.id=a.discussion_user
left join comments c on c.comment_post=a.discussion_id WHERE discussion_board='".$board['board_id']."'" 

---

Use SQL-92 join syntax (where you explicitly use the join) keyword to join, it makes the query more readable, especially after you start adding outer joins.

And yes, you want a left join to your comments table.