Joining the Same SQL Table Twice to Another Table

This past week I was porting some C# code to SQL. I ran into the need for a SQL technique I hadn’t seen before: joining a table twice to another table. The only way to explain is by way of example but let me preface by saying in my particular scenario I needed to replace two different column values in a table, with values looked up in a second table. Bear with me here. Suppose I have a table of "session data" like so:
create table tblSessions
sessionID int not null,
userID1 char(3) not null,
locationCode1 char(2) not null,
userID2 char(3) not null,
locationCode2 char(2) not null
insert into tblSessions values(111, ‘u11’, ‘L3’, ‘u44’, ‘L2’)
insert into tblSessions values(222, ‘u11’, ‘L1’, ‘u33’, ‘L1’)
insert into tblSessions values(333, ‘u33’, ‘L4’, ‘u22’, ‘L3’)
insert into tblSessions values(444, ‘u22’, ‘L1’, ‘u11’, ‘L2’)
insert into tblSessions values(555, ‘u55’, ‘L2’, ‘u22’, ‘L1’)
insert into tblSessions values(666, ‘u44’, ‘L3’, ‘u55’, ‘L4’)
So the first row of data means, "In session 111, user u11 who was at location L3 interacted with user u44 who was at location L2". Now suppose I want to replace both of the custom location columns (L3 and L2) with the state (such as California) that corresponds to the location code. For example, for the first session if location code L3 is somehow associated with Hawaii and location code L2 is associated with California, I want a SELECT statement which will produce:
sessionID  userID1  state  userID2  state
111        u11      HI     u44      CA
Let’s assume that the location codes correspond to postal zip codes and that zip codes correspond to states. So, suppose an (artificial) zip code to state table is:
create table tblZipAndState
zipCode int not null,
[state] char(2) not null
insert into tblZipAndState values(11111, ‘AL’)
insert into tblZipAndState values(22222, ‘CA’)
insert into tblZipAndState values(33333, ‘HI’)
insert into tblZipAndState values(44444, ‘MA’)
insert into tblZipAndState values(55555, ‘NY’)
This means zip code 11111 is in Alabama, and so on. Now there needs to be a link table to connect location codes to zip codes, so suppose:
create table tblJunction
locationCode char(2) not null,
zipCode int not null
insert into tblJunction values(‘L1’, 11111)
insert into tblJunction values(‘L2’, 22222)
insert into tblJunction values(‘L3’, 33333)
insert into tblJunction values(‘L4’, 44444)
Well one way to perform the double lookup-replacement is to join the tblSessions sessions data twice to the zip code and state tblZipAndState like this:
select S.sessionID, S.userID1, Z1.[state], userID2, Z2.[state]
tblSessions AS S
join tblJunction AS J1 on S.locationCode1 = J1.locationCode
join tblZipAndState AS Z1 on J1.zipCode = Z1.zipCode
join tblJunction AS J2 on S.locationCode2 = J2.locationCode
join tblZipAndState AS Z2 on J2.zipCode = Z2.zipCode
order by S.sessionID
The result is in the image below. Holy crud. The only way I could make sense of this was to draw a picture where there were two tblZipAndState tables. Of course there is only one table, but by JOINing twice and giving the table two names, Z1 and Z2, we can do a double replacement.
There may be other scenarios where joining a table twice to another table may be needed (but I kind of hope I don’t run into them!)
This entry was posted in Software Test Automation. Bookmark the permalink.