Databases
StackOverflow2013
Postsdbo
POhow to join 3 tables, topic, comment and user
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to join 3 tables, topic, comment and user
    primarykey
    data
    text
    <p>I have 3 tables, tbl_topic, tbl_comment, tbl_user. I want to select all the topics created by the user and also the topics that he commented on even if he is not the creator. Here is my database:</p> <pre><code>tbl_topic ---------- topic_id topic_title user_id tbl_comment ---------- comment_id comment_message user_id topic_id tbl_user ---------- user_id user_name </code></pre> <p>Need it so badly. Thanks!</p> <p>So far i got this</p> <pre><code>select * from tbl_topic T inner join tbl_comment C on T.topic_id = C.topic_id inner join tbl_user U on T.user_id = U.user_id GROUP BY T.topic_id </code></pre> <p>My problem is it only returns the topics that has comments on it. I want to include the topics created by the user even if it has 0 comments.</p> <p>I want the result to be like this:</p> <pre><code> +-----------+-----------+----------+-------------+----------------+----------+------- | topic_id | topic_title | user_id | comment_id | comment_message | user_id | topic_id | +-----------+-----------+----------+-------------+----------------+----------+-------- | 1 | my topic | 1 | 1 | comment me | 1 | 1 | 2 | others | 2 | 2 | comment me | 1 | 2 | 3 | my nocoment| 1 | NULL | NULL | NULL | NULL +-----------+---------+--------+-------------+----------+----------+---------+-------- ----------+-----------+ user_id | user_name | -----------+----------- 1 | me | 2 | someone | 1 | me -----------+---------+-- </code></pre> <p>I messed up with my fields in my tables, the user_id beside comment_message should be comment_user_id but i already created my database that way. Can you help make this possible?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoem Maranan
    1. USJoem Maranan
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POhow to join 3 tables, topic, comment and user
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POhow to join 3 tables, topic, comment and user
      1. This table or related slice is empty.
      1. VTUpMod
    1. COBetter paste what you have tried already..!
      singulars
      1. POhow to join 3 tables, topic, comment and user
      1. USMohammad Reza Saberi
    2. COYou are referring to the activities done by the user am I right?
      singulars
      1. POhow to join 3 tables, topic, comment and user
      1. USChristian Mark
    3. COif that's the case you have to use left outer join for comments, then UNION for the activities.
      singulars
      1. POhow to join 3 tables, topic, comment and user
      1. USChristian Mark
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload