Join 2 aggregate queries?

[Zak McGregor]

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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Fri, Feb 13, 2004 at 02:13:11 +0200,
Zak McGregor <za*@mighty.co.za> wrote:

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?

The simplest way given what you've got already is to make each of the
two selects a from item and join them on fixtureid.
That probably isn't the most efficient way to do it, but should get you
going for now.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

On Fri, 13 Feb 2004, Zak McGregor wrote:

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

Sure, make each a subselect and join those:
select * from

(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) as a

join

(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) as b

on (a.id=b.id);


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Zak McGregor]

On Fri, 13 Feb 2004 09:00:29 -0700 (MST)
"scott.marlowe" <sc***********@ihs.com> wrote:

Sure, make each a subselect and join those:

[snip]

Thanks Scoot, and Bruno too for his earlier suggestion of the same approach. I
have it working exactly as I needed it to, thanks!

Ciao

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend