求一条查询语句

一张活动表  一张作品表
怎么查询出所有活动的信息和他对应的作品数量

active表 id(主键) name
works表 id(主键) name aid(外键)

select id,[name],num from active as a join
(select aid,count(aid)as num from works group by aid) as b on a.id=b.aid

select * from active a left join works b on a.id=b.aid

select * from active as a ,works w where a.id=w.aid