Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Multiple Self Joins - Is there a better way?

Posted on 2004-10-20
Medium Priority
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.


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 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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