Join a table to itself in SQL

It recently came to my attention that some developers may not know how to join a table to itself. For them, I write this. Why would you want to do such a thing? Perhaps you have a company that is owned by another company, and you want to get any subsidiaries of a given company.

This first thing to note is that it is possible to have aliases in SQL. For example, if you want the "fname" field in a database to show up in your result set as "First_Name", you could do this:

SELECT	fname AS First_Name
FROM users
WHERE userID = 1
The same thing applies to tables in the FROM clause, except that you don't use the "AS" statement (which you could actually do without in the SELECT statement as well, but I like it). In this case, it doesn't effect your result set, but it does allow you to join a table to itself.
SELECT	child.CompanyID 
FROM companies parent, companies child
WHERE parent.CompanyID = child.ParentCompanyID
In this query we are querying the same table twice, once with the alias of "parent" and once with the alias of "child" (the names are arbitrary, I could have used "foo" and "bar"). Then you just use the aliases as though they were the proper names for the tables in the rest of the SQL statement.

That's it! Good luck!

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Really it's the simple way to join table it self

it solve me a problem take one day trials.

thanks aot.
# Posted By Hamdy Mohamed | 5/17/07 3:49 PM
Glad it helped!
# Posted By Steve Bryant | 5/18/07 8:26 PM
Thanks, very useful!
# Posted By Ash | 7/11/07 1:24 PM
Hmm... doesn't seem to work well with temporary tables. (Error: Can't reopen table: 't1')

Any thoughts?
# Posted By Mark | 6/19/11 3:48 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.