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_NameThe 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.
FROM users
WHERE userID = 1
SELECT child.CompanyIDIn 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.
FROM companies parent, companies child
WHERE parent.CompanyID = child.ParentCompanyID
That's it! Good luck!
it solve me a problem take one day trials.
thanks aot.
Any thoughts?