CODEWITHSUNDEEP
phpmysqlrelational-database
Fausto Gerlock
Fausto Gerlock

Reputation: 1

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

Upvotes: 0

Views: 186

Answers (3)

JohnK813
JohnK813

Reputation: 1124

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.

Upvotes: 3

Pradeep Singh
Pradeep Singh

Reputation: 3634

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

Upvotes: 1

Donnie
Donnie

Reputation: 46903

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.

Upvotes: 0

Related Questions