When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
The SQL Guru Answers your Questions...


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
     MessageText

Now, 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 the PostID of 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


Read Other SQL Guru Questions



ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article