Database relationships

Question

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_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?

http://stackoverflow.com/questions/419375/sql-join-differences — jball, Dec 08 '10 at 18:39
also http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins — jball, Dec 08 '10 at 18:40
Do you want just your SQL question answered, or do you want help with the relational database, the model ... you are going to experience this sort of issue all the time due to the model. — PerformanceDBA, Dec 11 '10 at 19:20

score 3 · Answer 1 · answered Dec 08 '10 at 18:39

3

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.

answered Dec 08 '10 at 18:39

JohnK813

1,134
7
13

score 1 · Answer 2 · answered Dec 08 '10 at 18:37

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']."'"

score 0 · Answer 3 · answered Dec 08 '10 at 18:35

0

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.

answered Dec 08 '10 at 18:35

Donnie

43,578
10
63
85

Database relationships

3 Answers3