CODEWITHSUNDEEP
sqldatabaseselect
MariaH
MariaH

Reputation: 341

How to create a query that returns forums by user

I have two tables on my db: forum and comment.

forum = id, user_id, message;

comment = id, user_id, forum_id, comment;

With this query I can return comments from the forums created by a user:

SELECT f.id as f_id, f.user_id as f_user_id, f.message,

       c.id as c_id, c.user_id as c_user_id, c.comment FROM

       forum f, comment c where

       c.forum_id=f.id and f.user_id= ? order by f.id;

However forums that have no comments does not show in the result set. I would like to prepare a query that returns all the forums (that have/not_have comments) by one user and the comments if they have them.

Can somebody help me with this? Thank you!

Upvotes: 0

Views: 29

Answers (1)

CMPS
CMPS

Reputation: 7769

The LEFT JOIN keyword returns all rows from the left table (forum_submission), with the matching rows in the right table (submission_comment). The result is NULL in the right side when there is no match. Source: http://www.w3schools.com/sql/sql_join_left.asp

   SELECT f.id as f_id, f.message, c.id as c_id, c.user_id as c_user_id, c.comment 
   FROM forum_submission f
   LEFT JOIN submission_comment c
   ON c.forum_id=f.id 
   WHERE f.user_id= ? 
   Order by f.id

Upvotes: 1

Related Questions