Today's question comes from Michael S.:
First of all, thank God for your return! I was looking for your contact information just two days ago and was disappointed that I couldn't find it. I have a general question that is probably considered fairly advanced. If possible, I'd like the answer to apply to Microsoft Access, Microsoft SQL Server 7.0, and Oracle 8i. I'd be most interested in Access and SQL.
Is there a way to do a "recursive join?" In other words, I'd like a table to link back to itself. For example, if I had a single table that stored messages for a message forum, and I wanted to query all posts from a particular thread, can I do it? Let's assume that the message table has the following fields:
PostID ParentPostID DatePosted AuthorName Subject MessageTextNow, I'd like to perform this query based on the initial (or root)
PostID(in this case, the ParentPostID would be 0). I'd like the query to return all records that are "linked" beneath it. As you can probably guess, ParentPostID would be a link to thePostIDof this post's parent.I know that I can accomplish this with multiple queries and/or tables, but it would be much more useful and elegant if I could link back onto itself.
Michael,
When you do a recursive join, you're joining the table to itself. To keep the query processor from getting confused, you need to use an 'alias' on the table.
Here's the T-SQL syntax for the type of query you described above.
SELECT foo
FROM Message M1 <-- 'M1' is the aliased name of the table
JOIN Message M2 <-- again, aliasing the message table,
now as 'M2'
ON M1.PostID = M2.ParentPostID
WHERE blah
|
That's all there is to it. The query processor treats M1 and M2 as two separate tables. You can also use:
SELECT foo FROM Message AS M1 JOIN Message AS M2 ON M1.PostID = M2.ParentPostID WHERE blah |
This also comes in handy if you have table names that are long. You can alias these to a character or two, which saves a lot of typing.
Sean