Join 2 aggregate queries?

Hi all

I have 2 aggregate queries, which are:

select f.id as fixtureid, t.name as home_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
and p.teamid=t.id and r.home=p.playerid and r.winner=p.playerid
group by fixtureid, t.name;

fixtureid | home_team | count
-----------+-----------+-------
2872 | Kat Fish | 12
2944 | The Fowls | 11

and

select f.id as fixtureid, t.name as away_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
and p.teamid=t.id and r.away=p.playerid and r.winner=p.playerid
group by fixtureid, t.name;

fixtureid | away_team | count
-----------+-------------+-------
2872 | A Cut Above | 13
2944 | Kat Fish | 14

I'd like to join them somehow to get the following:

fixtureid | home_team | count1 | away_team | count2
-----------+-----------+--------+-------------+-------
2872 | Kat Fish | 12 | A Cut Above | 13
2944 | The Fowls | 11 | Kat Fish | 14

Can anyone spot a reasonable way to do that please?

I have tried this, with predictably poor results:

select f.id as fixtureid, t.name as home_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
left outer join (select f2.id as fixtureid, t2.name as away_team, count(t2.name)
from teams t2, results r2, fixtures f2, playerstoteams p2
where f2.type=1 and f2.league=4 and r2.fixture=f2.id and t2.division=4
and p2.teamid=t2.id and r2.away=p2.playerid and r2.winner=p2.playerid
group by fixtureid, t2.name) as foo on (id=id)
where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
and p.teamid=t.id and r.home=p.playerid and r.winner=p.playerid
group by f.id, t.name;

I'd also like to not have to create a view or any temporary tables that would
need deleting afterwards.

Any help much appreciated, thanks!

Cheers

Zak
--
========================================================================
http://www.carfolio.com/ Searchable database of 10 000+ car specs
========================================================================