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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
LVL 77

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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.


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 77

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 77

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 77

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 77

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 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