Multiple Self Joins - Is there a better way?

Posted on 2004-10-20
Last Modified: 2012-08-14
I need to pull data out of an access database that is stored in 8 different rows (no I did not design this table and I cannot change the format). The first problem is there is no unique key, you have to concatenate 5 columns to identify it uniquely. I need to take the bulk of the data from one of the 8 rows, but also pull the different values from the price column from each of these 8 rows to display on a single line in the query. If anyone can help me, I'd be eternally grateful.
Question by:KNawrocki
  • 5
  • 3
  • 2
  • +1
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12362806
Can you provide a little more detail?  such as a table definition, some test data and expected results?
LVL 15

Expert Comment

ID: 12362875
Hi KNawrocki,

Perhaps some subqueries would meet your requirements?  Something like this?:

  , col2
  , col3
  , col4
  , col5
  , Price
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the first price comes from]) AS price2
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the second price comes from]) AS price3
  , (SELECT Price FROM yourTable WHERE [Conditions here that meet uniquely identify where the third price comes from]) AS price4
  , [etc for the other four columns]
FROM yourTable
  [Conditions here that meet uniquely identify where most of you data is coming from]

Author Comment

ID: 12368871
mcmonap -- Actually I have tried that and it works (slowly) for up to 6 self joins -- but then times out for the 8 joins.

slightwv --

Confidential data, I cannot give exacts, but here is an example of data types
ID Number      Type Name Exp      Edu      Area      YR      Hour
12345      Manual      2      1      Local      1      2.95
12345      Manual      2      1      Local      2      3.20
12345      Manual      2      1      Local      3      3.30
12345      Manual      2      1      Local      4      3.40
12345      Manual      2      1      Local      5      3.50
12345      Manual      2      1      Local      6      3.60
12345      Manual      2      1      Local      7      3.70
12345      Manual      2      1      Local      8      3.80
12345      Manual      2      1      Distant      1      3.00
12345      Manual      2      1      Distant      2      3.30
12345      Manual      2      1      Distant      3      3.40
12345      Manual      2      1      Distant      4      3.50
12345      Manual      2      1      Distant      5      3.60
12345      Manual      2      1      Distant      6      3.70
12345      Manual      2      1      Distant      7      3.80
12345      Manual      2      1      Distant      8      3.90
12345      Manual      5      2      Local      1      4.00
12345      Manual      5      2      Local      2      4.10
12345      Manual      5      2      Local      3      4.20
12345      Manual      5      2      Local      4      4.30
12345      Manual      5      2      Local      5      4.40
12345      Manual      5      2      Local      6      4.50
12345      Manual      5      2      Local      7      4.60
12345      Manual      5      2      Local      8      4.70
12345      Manual      5      2      Distant      1      4.00
12345      Manual      5      2      Distant      2      4.10
12345      Manual      5      2      Distant      3      4.20
12345      Manual      5      2      Distant      4      4.30
12345      Manual      5      2      Distant      5      4.40
12345      Manual      5      2      Distant      6      4.50
12345      Manual      5      2      Distant      7      4.60
12345      Manual      5      2      Distant      8      4.70

Now this data repeats for many additional ID_Numbers

I need to Disply
ID Number, Type Name, Exp, Edu, Area, Hour YR1, Hour YR2, Hour YR3, Hour YR4, Hour YR5, Hour YR6, Hour YR7, Hour YR8
All in one row
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Expert Comment

ID: 12369284
Here's an example of how to do this in UDB.  Not sure if the same functionality exists in Access.  It assumes combination of col1, col2, col3 are unique as you stated above for your five columns.  Give it a try!!

select col1,col2,
 max(case when col3 = '1' then col4 else null end) as year1,
 max(case when col3 = '2' then col4 else null end) as year2,
 max(case when col3 = '3' then col4 else null end) as year3
from test2
group by col1,col2

Here was my test table:

A        AA       1        11      
A        AA       2        22      
A        AA       3        33      
B        BB       1        110      
B        BB       2        220      
B        BB       3        330      

And the results of the query:

A   AA   11    22     33
B   BB   110   220   330      

LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12369445
Now that I see what you are after, I don't think you can get there from here in access.  This is a common question in the DB world.  A quick search (even just in this site) for "rows to columns" will yield many hits.  You just need to locate an example for Access.

You say there is no unique key on the table.  Why can't you add one or at least index the 1st 5 columns together?  This should make the self joins faster.  If this does not make performance acceptable, your only other option may be to write some VBA code to derive the results.

Also, how many rows are in this table (you may be pushing the limits of access)?  

I'll keep looking and if I find anything, I'll post back.

LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 250 total points
ID: 12369693
I've been unable to locate an exact example but have continued to play and here's what I have for you to try (It may not be any faster since I don't have that much data in my test case):

create a primary key on the 5 columns in your table
create 8 queries (1 for each year)
ex./ for year 1:  
   SELECT, Q.type, Q.exp,, Q.area, Q.yr, Q.hour FROM Q WHERE (((Q.yr)=1));

Go to relationships and set up a 5 column relationship from query year1 to each of the other 7 years queries.

Then create the final query as (this will paste very ugly, but should be straight forward in the query wizard):
SELECT, year1.type, year1.exp,, year1.area, year1.hour, year2.hour, year3.hour, year4.hour, year5.hour, year6.hour, year7.hour, year8.hour
FROM ((((((year1 INNER JOIN year2 ON (year1.area = year2.area) AND ( = AND (year1.exp = year2.exp) AND (year1.type = year2.type) AND ( = INNER JOIN year3 ON (year1.area = year3.area) AND ( = AND (year1.exp = year3.exp) AND (year1.type = year3.type) AND ( = INNER JOIN year4 ON (year1.area = year4.area) AND ( = AND (year1.exp = year4.exp) AND (year1.type = year4.type) AND ( = INNER JOIN year5 ON (year1.area = year5.area) AND ( = AND (year1.exp = year5.exp) AND (year1.type = year5.type) AND ( = INNER JOIN year6 ON (year1.area = year6.area) AND ( = AND (year1.exp = year6.exp) AND (year1.type = year6.type) AND ( = INNER JOIN year7 ON (year1.area = year7.area) AND ( = AND (year1.exp = year7.exp) AND (year1.type = year7.type) AND ( = INNER JOIN year8 ON (year1.area = year8.area) AND ( = AND (year1.exp = year8.exp) AND (year1.type = year8.type) AND ( =;


Author Comment

ID: 12372057
I first believed that the limits of access may be the problem. I have three similar tables, I started with the smallest that contains 3456 rows, the other two both have over 200,000 rows.

The one time I got this to work with 6 self joins, it took over two hours on the smallest table.

If I were to convert this to MS SQL, does anyone know of a SELECT that would work, or am I looking at importing to SQL and redesigning the tables as relational?

Expert Comment

ID: 12372142
Unless you have an index on the 5 unique columns, you're probably scanning through the entire table for each "iteration" of the query.  If you don't have an index, I suggest creating one (as a previous poster mentioned) and retrying it on the small table.  Then, a query like the one from mcmonap above should work fine.  Make sure you create it as a multiple-field index.
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12372210
200,000 rows is starting to push the limits of Access (MY OPINION!!!  There are those die-hards that will disagree)

in MS SQL and with the proper indexes as mentioned, the query from mcmonap or the original self-joins that you were talking about should perform OK.

For my own curosity:  Did you try out my last suggestion?  I'm wondering if runs any faster.........

Author Comment

ID: 12372859
slightwv --
Just got to finish putting that one together (got held up in meetings). Yes it worked. Now I've got to take this and publish using asp. I've always written my asp selects simply and outside of access. I guess another challenge is at hand!

Thanks - you've been a great help.
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12372926
For future reference:  At times it is better to use CODE to compile results.  Since you are using asp (I'm guessing you mean the Microsoft Web platform):  It might be better to perform a simple select and loop through the dataset to compile the report.

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question