A Challenge - Complex Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A Challenge - Complex Join

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2003-11-30 : 22:40:22

Greetings to all you gurus.

I think this may be a challenge to many. I need your help to critique/improve the SQL statement I have developed.

I have a DB which has a complex relationship among 3 tables. I need a SQL statement that will retireve all Assessment_data records for a given participant. Here is the best I have come up with:

 
SELECT p.Participant_PK, p.Full_Name, c.Chal_Year, 
        pu.Performance_Unit, ad.Assess_Date, <and more fields>
    FROM Participants p 
    INNER JOIN Register r           ON r.Participant_FK = p.Participant_PK
    INNER JOIN Challenges c         ON c.Chal_PK = r.Chal_FK
    INNER JOIN Assessments a        ON a.Chal_FK = c.Chal_PK
    INNER JOIN Assessment_Data ad   ON ad.Assess_FK = a.Assess_PK AND
                                       ad.Participant_FK = p.Participant_PK
    INNER JOIN Performance_Units pu ON pu.Perf_Unit_PK = r.Perf_Unit_FK
    WHERE p.Participant_PK = <nnn>
ORDER BY c.chal_Year

I wish I could paste a graphic to illustrate the relationships, but I'll try to describe as best I can.

Participants ==> Register <== Challenges ==> Assessments

Participants ==> Assessment_Data <=== Assessments

KEY: 1 ==> MANY

The key issue I have is that Assessment_Data has a Many-to-1 relationship with both Participants and Assessments.

So, the only solution I have come up with is the following JOIN:


    INNER JOIN Assessment_Data ad   ON ad.Assess_FK = a.Assess_PK AND
                                       ad.Participant_FK = p.Participant_PK

a = Assessments
p = Participants

This JOIN with keys from two different tables concerns me. Do I have a bad DB design? Is there a better design? Or is this the proper (best?) approach?

Here's a truncated description of the tables:


Participants
============
Participant_PK
FName
MI
LName
NickName
Full_Name
Perf_Unit_FK
...

Register
===============
Register_PK
Chal_FK
Participant_FK
Perf_Unit_FK
Location_FK
...

Challenges
===================
Chal_PK
Perf_Unit_FK
Chal_Year
Start_Date
End_Date
Chal_Title
...

Assessments
================
Assess_PK
Chal_FK
Assess_Type_FK
Assess_Title
...

Assessment_Data
==================
AD_PK
Assess_FK
Participant_FK
Assess_Date
Comments
Height
Weight
....

I have tried to be brief, so I may have left something out. I am more than happy to provde additional details if needed.

TIA.

Best Regards,
Jim

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-30 : 22:47:10

Jim,

To really help us out, can you post the DDL (CREATE TABLE etc..) and DML (INSERT blah) statements.
That way we can simply copy and paste into our dev boxes and start helping.

DavidM

"SQL-3 is an abomination.."

Apollois
Starting Member

49 Posts

Posted - 2003-11-30 : 23:06:14

Hi Dave,

Thanks for the quick feedback.

I really can't post any data because it's sensitive.

I could post the DDL if you really need it, but I don't think this is a nuts and bolts issue. It's more of a high-level design issue, so I'm not sure the DDL would really help.

Perhaps I have not normalized the data properly. The thorn I'm dealing with is the fact that the Assessment_Data table has two parent tables: Participants and Assessments. I haven't run across this issue before, so I'm not sure what's the best way to deal with it.

Focus on the JOIN for the Assessment_Data table. Does that look right to you? Is it unusual? Or is it to be expected?

Thanks,
Jim

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-30 : 23:25:17

Jim,

We don't actual expect you to provide real data, or in fact the whole schema. Just the nuts and bolts (PK, FK's).

Anywho...Try to break it down into small parts...

Tell me if any thing is wrong with this...

1) A Participant has many Registers
2) A Challenge has many Registers.
3) Given 1 & 2 - A Participant has many Challenges

4) A Participant has many Assement_Data
5) A Assement has many Assessment_Data
6) A Challenge has many Assessments
7) Given 4,5 & 6 leads you straight back to point 3

That seems strange to me.

DavidM

"SQL-3 is an abomination.."

Apollois
Starting Member

49 Posts

Posted - 2003-11-30 : 23:56:42

quote:
Originally posted by byrmol
We don't actual expect you to provide real data, or in fact the whole schema. Just the nuts and bolts (PK, FK's).

I listed the tables with PKs and FKs in my original post.
Don't have any dummy data, just a subset of the real data.

Your assessment is basically correct - it appears to be a circular relationship. This is what bothers me, but I don't see any way around it.

Here is the comlete scenerio.

An organization has set up health challenges -- programs to help improve the health/fitness of their employees.

There are many challenges:
One for each Performance Unit for each year

Not all PUs have a challenge every year
Not all employees participate every year.

So, for a given year, a Challenge record is created for each PU that wants a program. A CHALLENGE may consist of one or more ASSESSMENTS. An Assessment is a constrained period of time during which all participants must be tested. There may be up to 3 Assessments per Challenge: Baseline, Midpoint, Final.

Then if an employee wants to participate, they must REGISTER for the Challenge in their PU.

Generally, the PARTICIPANTS table is the list of employees who have participated at one time or another (this has been going on for several years now). However, an employee may participate one year, but not the next year.

When an employee wants to participate, the Participants table is checked for a record for that employee. A new record is added if none is found.

Then, the employee (Participant) is REGISTERED for the Challenge in their PU. A record is entered into the REGISTER table that links the Participant with the specific Challenge (PU and Year).

The next step is Assessment. The Participant is tested, and data entered into the Assessment_Data table, one record for each Assessment.

So the ASSESSMENT_DATA table has one record for each Participant/Assessment. Remember that there are there are one or more Assessments per Challenge, and a Challenge for each PU for each year. So, the Assessment_Data record is for:
One Participant
For a given Challenge Year
For a given Assessment

As I try to describe this, I am realizing that the data must not be properly normalized. The relationships are just too twisted to describe clearly.

I hope this helps. Your thoughts are welcome.

Best Regards,
Jim

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-01 : 00:12:36

Jim,

When we ask for table schema (subset or the entire thing) we are really asking for the CREATE TABLE statements. We can then bang them into SQL and use a ERD tool to inspect..

I think you have almost solved it yourself...

The ASSESSMENT_DATA contains the Key of the Participant, Challenge and Assessment. Which appears to be just the Register Key plus the Assessment Key, which by the why should probably be Chal_FK & Assess_Type_FK.

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources