Solved

Multiple Self Joins - Is there a better way?

Posted on 2004-10-20
11
313 Views
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.
0
Comment
Question by:KNawrocki
  • 5
  • 3
  • 2
  • +1
11 Comments
 
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?
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12362875
Hi KNawrocki,

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

SELECT col1
  , 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
WHERE
  [Conditions here that meet uniquely identify where most of you data is coming from]
0
 

Author Comment

by:KNawrocki
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
0
 

Expert Comment

by:bpricefnni
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      



0
 
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.

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

Accepted Solution

by:
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.id, Q.type, Q.exp, Q.edu, 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.id, year1.type, year1.exp, year1.edu, 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 (year1.edu = year2.edu) AND (year1.exp = year2.exp) AND (year1.type = year2.type) AND (year1.id = year2.id)) INNER JOIN year3 ON (year1.area = year3.area) AND (year1.edu = year3.edu) AND (year1.exp = year3.exp) AND (year1.type = year3.type) AND (year1.id = year3.id)) INNER JOIN year4 ON (year1.area = year4.area) AND (year1.edu = year4.edu) AND (year1.exp = year4.exp) AND (year1.type = year4.type) AND (year1.id = year4.id)) INNER JOIN year5 ON (year1.area = year5.area) AND (year1.edu = year5.edu) AND (year1.exp = year5.exp) AND (year1.type = year5.type) AND (year1.id = year5.id)) INNER JOIN year6 ON (year1.area = year6.area) AND (year1.edu = year6.edu) AND (year1.exp = year6.exp) AND (year1.type = year6.type) AND (year1.id = year6.id)) INNER JOIN year7 ON (year1.area = year7.area) AND (year1.edu = year7.edu) AND (year1.exp = year7.exp) AND (year1.type = year7.type) AND (year1.id = year7.id)) INNER JOIN year8 ON (year1.area = year8.area) AND (year1.edu = year8.edu) AND (year1.exp = year8.exp) AND (year1.type = year8.type) AND (year1.id = year8.id);


0
 

Author Comment

by:KNawrocki
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?
0
 

Expert Comment

by:bpricefnni
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.
0
 
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.........
0
 

Author Comment

by:KNawrocki
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.
0
 
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now