SQL: Join Parent - Child tables

Question

I'm building a simple review website application and need some help with SQL Query.

There are 3 tables (Topics, Comments, Users). I need a SQL query to select the data from all 3 tables.

The 'Topics' table is the parent and the 'Comments' table contains the child records (anywhere from zero to 100 records per parent.

The third table 'Users' contains the user information for all users.

Here are the fields for the 3 tables:

Topics (topicID, strTopic, userID)
Comments (commentID, topicID, strComment, userID)
Users (userID, userName)

I tried:

SELECT * 
FROM   Topics 
  Inner Join Comments ON Topics.topicID = Comments.topicID
  Inner Join Users ON Topics.userID = Users.userID

But this does not work correctly because there are multiple topics and the User info is not joined to the Comments table. Any help would be appreciated.

Do you want the results to include topics that have no comments? — HABO, Jul 09 '12 at 20:57
What are you trying to accomplish? Do you want to find all activity for a user? All comments made on a particular topic, including usernames? Also, it's not usually a good idea to prefix/suffix column names with the variable type (so, don't use `str...`). You may also benefit from attaching timestamps to your tables, for a number of reasons. — Clockwork-Muse, Jul 09 '12 at 20:58
The result should be the list of all a particular users topics with each comment (if there are any) underneath the topic. The DB schema in my expample was simplified for brevity, there are timestamps for both the topics and comments as well as other fields. I'm not sure how this can be done iterating through one recordset. — pray4Mojo, Jul 10 '12 at 14:14

score 1 · Accepted Answer · answered Jul 09 '12 at 21:13

1

You should do left join with Comment to get Topics with no comments and also join Topic and Comment with Users to get related user information for both.

SELECT * 
FROM Topics t
INNER JOIN Users tu on tu.userID = t.userID
LEFT JOIN Comments c on c.topicID = t.topicID
LEFT JOIN User cu on cu.userID = c.userID

answered Jul 09 '12 at 21:13

Satish

3,020
7
35
47

This works except that when there are no comments the query is missing some data from the Topics table. – pray4Mojo Jul 10 '12 at 23:51
It's actually not missing data, rather the userID thats returned is the userID from the Topics table, even when there is a Comment record. – pray4Mojo Jul 11 '12 at 00:28
That shouldn't be the case. Maybe the topic userId and comment userId are the same that's why its returning the same twice. – Satish Jul 11 '12 at 16:23
That was the problem. I added an alias for the field and it's all working fine. – pray4Mojo Jul 12 '12 at 16:54

score 0 · Answer 2 · answered Jul 09 '12 at 20:49

0

You need to join to the user table twice.

SELECT *
FROM Topics
 INNER JOIN Comments ON Topics.topicID = Comments.topicID
 INNER JOIN Users AS u1 ON Topics.userID = u1.userID
 INNER JOIN Users AS u2 ON Comments.userID = u2.userID

answered Jul 09 '12 at 20:49

Levi W

805
6
13

SQL: Join Parent - Child tables

2 Answers2