Mulitple table joins on same row

[kingcol]

Hi, I have several tables which I would like to join together and create a result set on a row by row basis. The tables I have are:

academic_progress_tbl
academic_progess_id (pri)
pupil_id
subject_id
score
target


Subjects_tbl
subject_id (pri)
subject_name

Pupil_tbl
pupil_id
name

======= DATA
Academic_progress_tbl:
1 - 1 - 4 - 11a - 15
2 - 1 - 5 - 7 - 12

Subjects_tbl:
1 - English
2 - Maths
3 - Science
4 - ICT
5 - Business Studies

Pupil_tbl
1 - Colin
2 - Mark

===

I want to be able to produce a query which shows the scores from all subject that a user has taken (in the academic_progress_tbl) and display them in one row, ie:

PUPIL NAME, SUBJECT 1, SCORE1, TARGET1, SUBJECT2, SCORE2, TARGET2 etc...

grouped by PUPIL ID (so one row for each unique pupil ID).

Anyone got any ideas?