SELECT opp_teamID_FK,teamID_FK,score,date,t1.name as name,t2.name as othername from score
JOIN team t1 ON score.teamID_FK = t1.teamID
JOIN team t2 ON score.opp_teamID_FK = t2.teamID
where score.teamID_FK=23 OR score.opp_teamID_FK=23
gives:
opp_teamID_FK teamID_FK score date name othername
25 23 1770 2007-10-27 Team 1 Team 2
30 23 1210 2007-09-29 Team 1 Team 3
29 23 2080 2008-01-20 Team 1 Team 4
23 25 1970 2007-10-27 Team 2 Team 1
23 29 900 2008-01-20 Team 4 Team 1
23 30 1650 2007-09-29 Team 3 Team 1
I want instead:
Team 1 (tid is 23)
2007-09-29 1210 Team 3 1650
2007-10-27 1770 Team 2 1970
2008-01-20 2080 Team 4 900