VB and VBA Users Source Code: Using self joins to return related data from the same table
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Using self joins to return related data from the same table
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, November 11, 2002
Hits:
920
Category:
Database/SQL/ADO
Article:
A "Self Join" is a join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. For example, you can use a self-join to find out the authors who have the same age and post code. Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. Below is an example of a self-join: --Table Defination Create Table Authors( AuthorID numeric(10,0) identity not null, FirstName Varchar(50), LastName Varchar(50), PostCode Varchar(20), Age int ) go --Add Authors insert Authors(FirstName, LastName, PostCode, Age) values ("Andrew", "Baker", "W12", 31) insert Authors(FirstName, LastName, PostCode, Age) values ("John", "Randel", "W11", 28) insert Authors(FirstName, LastName, PostCode, Age) values ("Phil", "Harte", "W1", 31) insert Authors(FirstName, LastName, PostCode, Age) values ("Steven", "Brown", "NW13", 12) insert Authors(FirstName, LastName, PostCode, Age) values ("Dylan", "Ward", "M11", 8) insert Authors(FirstName, LastName, PostCode, Age) values ("Ben", "Dover", "W1", 5) insert Authors(FirstName, LastName, PostCode, Age) values ("Bart", "Simpson", "LE2", 23) --Return all authors who have the same postcode and are the same age. SELECT au1.FirstName, au1.LastName, au2.FirstName, au2.LastName, au1.PostCode, au1.Age FROM Authors au1, Authors au2 --Self Join Tables WHERE (au1.PostCode = au2.PostCode and au1.Age = au2.Age) --Join criteria AND au1.LastName < au2.LastName --Remove duplicate values ORDER BY au1.LastName ASC, au1.FirstName ASC --Sort --Clean up SQL --Drop Table Authors
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
