I have data in 2 tables, and I want to create a report.

Table A:

• tableAID (primary key)
• name

Table B:

• tableBID (primary key)
• grade
• tableAID (foreign key, references Table A)

There is much more to both tables, but those are the relevant columns.

The query I want to run, conceptually, is this:

select TableA.name, avg(TableB.grade) where TableB.tableAID = TableA.tableAID

The problem of course is that I'm using an aggregate function (avg), and I can rewrite it like this:

select avg(grade), tableAID from TableB group by tableAID

but then I only get the ID of TableA, whereas I really need that name column which appears in TableA, not just the ID.

Is it possible to write a query to do this in one statement, or would I first need to execute the second query I listed, get the list of id's, then query each record in TableA for the name column... seems to me I'm missing something obvious here, but I'm (quite obviously) not an sql guru...