I've been on a kick about posting on joining tables lately. Sometimes you will come across a situation where you need to join a table to itself when executing a query. You can do this simply by aliasing your tables and using an inner join. There is no 'standard' scenario to explain this concept, so I decided to use a basic forum messaging system as our scenario.
Problem: Design a database where people can make posts, and people can comment on other people's post. Keep in mind that people can also comment on other peoples comments. Only use one table.
Let's start by deciding how our table will be. We want to keep track of the user who posted the comment, the subject and body of the comment, the date the comment was posted, and if the post is a comment, the comment that the post applies to.
Posts
-----------
postID PK
postedBy VARCHAR(50)
subject VARCHAR(50)
body VARCHAR(500) -- Note: this is an unusually large size for a comment's size.
datePosted DATE
parentPost INT
So, at this point we can create our database and table:
USE master;
GO
CREATE DATABASE Forum;
GO
USE Forum;
-- Create a Posts table.
CREATE TABLE Posts
(
postID INT IDENTITY(1,1) PRIMARY KEY
, postedBy VARCHAR(50)
, [subject] VARCHAR(50)
, body VARCHAR(500)
, datePosted DATE
, parentPost INT -- This field will be used for comments on our posts.
);
Next, we will insert some records to play with:
-- Insert some test records into our Posts table.
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message'
, 'This is test message.'
, '3/17/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message #2'
, 'This is another test message.'
, '3/18/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message #3'
, 'This is yet another test message.'
, '3/18/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Good post!'
, 'That was a good post'
, '3/18/2011'
, 1
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Great post!'
, 'That was a great post'
, '3/18/2011'
, 1
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Lalalala'
, 'allalalalala'
, '3/18/2011'
, 2
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Bad Post'
, 'That was a bad post.'
, '3/18/2011'
, 3
);
Now we got to think about how to get our original posts. Realize that if it is a normal post, it will not have a parentPost because it is not a comment on someone else's post. So we will start there:
-- Get all parent posts.
SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On'
FROM Posts AS P
WHERE P.parentPost IS NULL;
 |
| Results of previous query. |
Now, let's get all the posts that have comments associated with them. We will do this by creating an inner join from our Posts table, back to our Posts table. What you will need to is create two aliases for your table to compare back to itself. Let's learn by example:
-- Let's retrieve all posts that have comments.
SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted By', C.[subject] AS 'Comment'
FROM Posts AS P
INNER JOIN Posts AS C
ON P.postID = C.parentPost;
In the above query, we are essentially referencing two copies of the Posts table, I called them P and C in our query. The query uses a simple inner join to grab all records from our C Posts table that have a parentPost value other than null.
The query results look like:
 |
| Results of our comments query. |
You can use this technique to perform quite a range of cool things, such as comparing a table to itself to narrow query results
Referring back to the posts example, we can use another technique (called sub querying, which will be explained in future post) to obtain posts and the number of comments on that post:
SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
FROM Posts AS P
WHERE P.parentPost IS NULL;
The results look like this:
 |
| Results of our sub-query retrieving posts and the number of comments. |
Full script:
USE master;
GO
CREATE DATABASE Forum;
GO
USE Forum;
-- Create a Posts table.
CREATE TABLE Posts
(
postID INT IDENTITY(1,1) PRIMARY KEY
, postedBy VARCHAR(50)
, [subject] VARCHAR(50)
, body VARCHAR(500)
, datePosted DATE
, parentPost INT -- This field will be used for comments on our posts.
);
-- Insert some test records into our Posts table.
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message'
, 'This is test message.'
, '3/17/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message #2'
, 'This is another test message.'
, '3/18/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
)
VALUES
(
'Self Destruct'
, 'Test Message #3'
, 'This is yet another test message.'
, '3/18/2011'
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Good post!'
, 'That was a good post'
, '3/18/2011'
, 1
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Great post!'
, 'That was a great post'
, '3/18/2011'
, 1
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Lalalala'
, 'allalalalala'
, '3/18/2011'
, 2
);
INSERT INTO Posts
(
postedBy
, [subject]
, body
, datePosted
, parentPost
)
VALUES
(
'Self Destruct'
, 'Bad Post'
, 'That was a bad post.'
, '3/18/2011'
, 3
);
-- Let's retrieve all parent posts and the number of comments that post has.
-- Note: This query contains a subquery.
SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
FROM Posts AS P
WHERE P.parentPost IS NULL;
-- Let's retrieve all posts that have comments.
SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted By', C.[subject] AS 'Comment'
FROM Posts AS P
INNER JOIN Posts AS C
ON P.postID = C.parentPost;
