Problem with multiple joins

[SeaviewBlue]

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:

SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle FROM Results 
INNER JOIN MeetEvents on Results.EventID = MeetEvents.EventNameID
INNER JOIN Students on Results.Student1 = Students.ID 
WHERE Results.MeetID = 80
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
 

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

[MMcCarthy]

The primary key in students table is StudentID not ID

SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
FROM ((Results INNER JOIN MeetEvents 
ON Results.EventID = MeetEvents.EventNameID)
INNER JOIN Students 
ON Results.Student1 = Students.StudentID) 
WHERE Results.MeetID = 80
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
 

Mary

[SeaviewBlue]

The primary key in students table is StudentID not ID

SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
FROM ((Results INNER JOIN MeetEvents 
ON Results.EventID = MeetEvents.EventNameID)
INNER JOIN Students 
ON Results.Student1 = Students.StudentID) 
WHERE Results.MeetID = 80
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder 
 

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

[MMcCarthy]

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

[SeaviewBlue]

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

[MMcCarthy]

Try this and see if it does what you want.

SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
FROM (((((Results INNER JOIN MeetEvents 
ON Results.EventID = MeetEvents.EventNameID)
LEFT JOIN Students As S1 ON Results.Student1 = S1.StudentID) 
LEFT JOIN Students As S2 ON Results.Student1 = S2.StudentID)
LEFT JOIN Students As S3 ON Results.Student1 = S3.StudentID)
LEFT JOIN Students As S4 ON Results.Student1 = S4.StudentID)
WHERE Results.MeetID = 80
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder

[SeaviewBlue]

Try this and see if it does what you want.

SELECT Results.*, MeetEvents.*, Students.First, Students.Last, Students.Middle 
FROM (((((Results INNER JOIN MeetEvents 
ON Results.EventID = MeetEvents.EventNameID)
LEFT JOIN Students As S1 ON Results.Student1 = S1.StudentID) 
LEFT JOIN Students As S2 ON Results.Student1 = S2.StudentID)
LEFT JOIN Students As S3 ON Results.Student1 = S3.StudentID)
LEFT JOIN Students As S4 ON Results.Student1 = S4.StudentID)
WHERE Results.MeetID = 80
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:

SELECT Results.*, MeetEvents.*, 
S1.First & S1.Middle & S1.Last AS S1Name, 
S2.First & S2.Middle & S2.Last AS S2Name, 
S3.First & S3.Middle & S3.Last AS S3Name, 
S4.First & S4.Middle & S4.Last AS S4Name 
FROM (((((Results INNER JOIN MeetEvents ON Results.EventID = MeetEvents.EventNameID) 
LEFT JOIN Students As S1 ON Results.Student1 = S1.ID) 
LEFT JOIN Students As S2 ON Results.Student2 = S2.ID) 
LEFT JOIN Students As S3 ON Results.Student3 = S3.ID) 
LEFT JOIN Students As S4 ON Results.Student4 = S4.ID) 
WHERE Results.MeetID = 80 
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
 

Thank you once again - Your help has been greatly appreciated.

SeaviewBlue

[MMcCarthy]

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:

SELECT Results.*, MeetEvents.*, 
S1.First & S1.Middle & S1.Last AS S1Name, 
S2.First & S2.Middle & S2.Last AS S2Name, 
S3.First & S3.Middle & S3.Last AS S3Name, 
S4.First & S4.Middle & S4.Last AS S4Name 
FROM (((((Results INNER JOIN MeetEvents ON Results.EventID = MeetEvents.EventNameID) 
LEFT JOIN Students As S1 ON Results.Student1 = S1.ID) 
LEFT JOIN Students As S2 ON Results.Student2 = S2.ID) 
LEFT JOIN Students As S3 ON Results.Student3 = S3.ID) 
LEFT JOIN Students As S4 ON Results.Student4 = S4.ID) 
WHERE Results.MeetID = 80 
ORDER BY Results.sLevel, MeetEvents.EventTypeID, MeetEvents.DisplayOrder
 

Thank you once again - Your help has been greatly appreciated.

SeaviewBlue

You're welcome.