Joins to the same table

[chinkygogo]

Hi there,

if i have a table with columns:
Teacher ID1 | Teacher Comment1 | Teacher ID2 | Teacher Comment 2 |

i also have another table that links the teacher ID with their names called [Staff Profiles]

how do i create a query that returns the names of both teacher 1 & 2. i have tried:

SELECT *
FROM [Subject Assessment] INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID1]=[Staff Profiles].[Teacher ID]) INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID2]=[Staff Profiles].[Teacher ID];

This throws an error. I have tried Aliasing but this also throws an error.

don't know whether this makes a difference, but the table which i gave was a join in the first place.....

i.e. teacherID1 | Teacher Comment1 is the tutor report of which there is one per student
teacher ID2 | teacher Comment2 is the subject report of which there are many per student


thanks in advance

 

[pdx_man]

Just to confirm, your query utilizing aliases looked like:

SELECT *
FROM [Subject Assessment] sa INNER JOIN [Staff Profiles] sp1 ON sa.[Teacher ID1]=sp1.[Teacher ID] INNER JOIN [Staff Profiles] sp2 ON sa.[Teacher ID2]=sp2.[Teacher ID];

If you are typing this in vs using the QBE, your error may be in the closing paren without an opening:

SELECT *
FROM [Subject Assessment] INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID1]=[Staff Profiles].[Teacher ID]) INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID2]=[Staff Profiles].[Teacher ID];

Unless this is not a copy and paste from the original query ...

What is the exact error message you are getting?

 

[chinkygogo]

I obviously wasn't using my aliasing properly, your example seems to have sorted it out!

thank you