471,354 Members | 1,489 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Problem with multiple joins

Howdy folks,

I am new to the site and new to joins, so I'm hoping I can get some much needed assistance.

Using ASP, VBscript & an Access 2003 db, I am building a page to display results from track and field events, and am struggling with joining information from 3 tables.

I will try to explain what I have and what I am doing as clearly as possible.

Table 1 - Results
MeetID - Field used to select records from Results table
sLevel - Include here only because it is mentioned in the code
EventID - Relation: MeetEvents.EventNameID
Student1 - Relation: Students.ID
Student2 - Relation: Students.ID
Student3 - Relation: Students.ID
Student4 - Relation: Students.ID

Table 2 - MeetEvents
EventNameID - Key
EventName
DisplayOrder
EventTypeID

Table 3 - Students
StudentID - Key
First
Last
Middle

Select Statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle FROM Results 
  2. INNER JOIN MeetEvents on Results.EventID = MeetEvents.EventNameID
  3. INNER JOIN Students on Results.Student1 = Students.ID 
  4. WHERE Results.MeetID = 80
  5. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
  6.  
Error Received:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'Results.EventID = MeetEvents.EventNameID INNER JOIN Students on Results.Student1 = Students.ID'.

Additional information:
EventID will always exist.
Student1 Will always match a record in Students.ID.
Student2-4 Will be zero if it was a single student event, otherwise, each will match a StudentID (The code does not reflect this, because until I've figured out how to get the information for Student1, I haven't really considered how I will get the information for Student2-4).

I hope that this is enough (but not too much) information and that somebody can help.

Thank you.

SeaviewBlue
Apr 12 '07 #1
7 6155
MMcCarthy
14,534 Expert Mod 8TB
The primary key in students table is StudentID not ID

Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
  2. FROM ((Results INNER JOIN MeetEvents 
  3. ON Results.EventID = MeetEvents.EventNameID)
  4. INNER JOIN Students 
  5. ON Results.Student1 = Students.StudentID) 
  6. WHERE Results.MeetID = 80
  7. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
  8.  
Mary
Apr 13 '07 #2
The primary key in students table is StudentID not ID

Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
  2. FROM ((Results INNER JOIN MeetEvents 
  3. ON Results.EventID = MeetEvents.EventNameID)
  4. INNER JOIN Students 
  5. ON Results.Student1 = Students.StudentID) 
  6. WHERE Results.MeetID = 80
  7. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
  8.  
Mary
Thank you Mary,

It appears that properly parenthesizing the join statements did the trick.

The StudentID vs ID was my mistake - ID is the actual key, but StudentID is another field in the table that is not being used.

I seem to be lost on how to parenthesize the join statements, but I'm going to experiment now to see if I can get the Student2-4 fields joined in.

Thanks Again!

SeaviewBlue
Apr 13 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Thank you Mary,

It appears that properly parenthesizing the join statements did the trick.

The StudentID vs ID was my mistake - ID is the actual key, but StudentID is another field in the table that is not being used.

I seem to be lost on how to parenthesize the join statements, but I'm going to experiment now to see if I can get the Student2-4 fields joined in.

Thanks Again!

SeaviewBlue
You're welcome
Apr 13 '07 #4
Thank you Mary,

It appears that properly parenthesizing the join statements did the trick.

The StudentID vs ID was my mistake - ID is the actual key, but StudentID is another field in the table that is not being used.

I seem to be lost on how to parenthesize the join statements, but I'm going to experiment now to see if I can get the Student2-4 fields joined in.

Thanks Again!

SeaviewBlue
I am sorry to report that I have not been able to figure out how to join information from the Students table for Student2-4.

These 3 fields will only find a match on Students.ID when Results.StudentX > 0.

Once again, any assistance would be greatly appreciated.

Thank you,

SeaviewBlue
Apr 15 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Try this and see if it does what you want.

Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
  2. FROM (((((Results INNER JOIN MeetEvents 
  3. ON Results.EventID = MeetEvents.EventNameID)
  4. LEFT JOIN Students As S1 ON Results.Student1 = S1.StudentID) 
  5. LEFT JOIN Students As S2 ON Results.Student1 = S2.StudentID)
  6. LEFT JOIN Students As S3 ON Results.Student1 = S3.StudentID)
  7. LEFT JOIN Students As S4 ON Results.Student1 = S4.StudentID)
  8. WHERE Results.MeetID = 80
  9. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
Apr 16 '07 #6
Try this and see if it does what you want.

Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
  2. FROM (((((Results INNER JOIN MeetEvents 
  3. ON Results.EventID = MeetEvents.EventNameID)
  4. LEFT JOIN Students As S1 ON Results.Student1 = S1.StudentID) 
  5. LEFT JOIN Students As S2 ON Results.Student1 = S2.StudentID)
  6. LEFT JOIN Students As S3 ON Results.Student1 = S3.StudentID)
  7. LEFT JOIN Students As S4 ON Results.Student1 = S4.StudentID)
  8. WHERE Results.MeetID = 80
  9. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
Mary - The join's were perfect!

With a little tweaking, here is the code I end up with, that actually gives me what I was looking for:
Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, 
  2. S1.First & S1.Middle & S1.Last AS S1Name, 
  3. S2.First & S2.Middle & S2.Last AS S2Name, 
  4. S3.First & S3.Middle & S3.Last AS S3Name, 
  5. S4.First & S4.Middle & S4.Last AS S4Name 
  6. FROM (((((Results INNER JOIN MeetEvents ON Results.EventID = MeetEvents.EventNameID) 
  7. LEFT JOIN Students As S1 ON Results.Student1 = S1.ID) 
  8. LEFT JOIN Students As S2 ON Results.Student2 = S2.ID) 
  9. LEFT JOIN Students As S3 ON Results.Student3 = S3.ID) 
  10. LEFT JOIN Students As S4 ON Results.Student4 = S4.ID) 
  11. WHERE Results.MeetID = 80 
  12. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
  13.  
Thank you once again - Your help has been greatly appreciated.

SeaviewBlue
Apr 16 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Mary - The join's were perfect!

With a little tweaking, here is the code I end up with, that actually gives me what I was looking for:
Expand|Select|Wrap|Line Numbers
  1. SELECT Results.*, MeetEvents.*, 
  2. S1.First & S1.Middle & S1.Last AS S1Name, 
  3. S2.First & S2.Middle & S2.Last AS S2Name, 
  4. S3.First & S3.Middle & S3.Last AS S3Name, 
  5. S4.First & S4.Middle & S4.Last AS S4Name 
  6. FROM (((((Results INNER JOIN MeetEvents ON Results.EventID = MeetEvents.EventNameID) 
  7. LEFT JOIN Students As S1 ON Results.Student1 = S1.ID) 
  8. LEFT JOIN Students As S2 ON Results.Student2 = S2.ID) 
  9. LEFT JOIN Students As S3 ON Results.Student3 = S3.ID) 
  10. LEFT JOIN Students As S4 ON Results.Student4 = S4.ID) 
  11. WHERE Results.MeetID = 80 
  12. ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
  13.  
Thank you once again - Your help has been greatly appreciated.

SeaviewBlue
You're welcome.
Apr 16 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Prem | last post: by
8 posts views Thread by Matt | last post: by
16 posts views Thread by Randy Harris | last post: by
2 posts views Thread by narendra vuradi | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.