problems repeating a relational exhcange in mysql

[ihcra]

hi,

i am trying to construct a mysql query that references a list of members froma members table 3 times to link to three column IDs in another which stores teams

table 1

memberID, membername

table 2

teamID, jobID, team_member1_ID, team_member2_ID, team_member3_ID

what i have tried so far which in my head seemed logical was

SELECT membername AS team_member1 FROM table1, table2 WHERE team_member1_ID=memberID 
UNION ALL 
SELECT membername AS team_member2 FROM table1, table2 WHERE team_member2_ID=memberID 
UNION ALL
SELECT membername AS team_member3 FROM table1, table2 WHERE team_member3_ID=memberID 
ORDER BY jobID

what i hope to get back is

jobID | team_member1 | team_member2 | team_member3 
 1    | bob          | carole       | frances
 2    | bob          | tom          | nigel
 3    | dave         | bob          | NULL

but instead i get

jobID | team_member1
 1    | bob
 1    | carole
 1    | frances
 2    | bob
 2    | tom
 2    | nigel
 3    | dave
 3    | bob

any ideas?

is there a magical function that is ideal but i have missed it?

[barand]

Try

SELECT t2.jobID, tm1.membername as name1, 
tm2.membername as name2, 
tm3.membername as name3
FROM table2 t2 
LEFT JOIN table1 tm1
on t2.team_member1_ID = tm1.memberID
LEFT JOIN table1 tm2
on t2.team_member2_ID = tm2.memberID
LEFT JOIN table1 tm3
on t2.team_member3_ID = tm3.memberID

[ihcra]

thats excellent thanks

i hadnt assumed you could refer to something that you hadnt yet aliased within the query

thanks again - super