First imagine the 2 queries were just tables. You would do this:
select a.producer, a.firstquerycolumn, b.secondquerycolumn
from table1 a
join table2 b on b.producer = a.producer
You can replace each table by a query (known as an in-line view):
select a.Prod, a.AnimalsBought, b.AnimalsExploration
from
( select Producers.name Prod, count(Animals.idanimal) AnimalsBought
from AnimalsBought, Animals, Producers
where (AnimalsBought.idanimal = Animals.idanimal)
and (Animals.owner = Producers.nif)
group by Producers.name
) a
join
( select Producers.name Prod, count(Animals.idanimal) AnimalsExploration
from AnimalsExploration, Animals, Producers
where (AnimalsExploration.idanimal = Animals.idanimal)
and (Animals.owner = Producers.nif)
group by Producers.name
) b
on a.Prod = b.Prod;
You may need to change my "join" to "full outer join" if one query may return data for a producer where the other does not. I would also be inclined to restructure the query as follows, making a main query on Producers outer joined to the 2 subqueries (with Producers removed):
select Producers.name Prod, a.AnimalsBought, b.AnimalsExploration
from Producers
left outer join ( select Animals.owner, count(AnimalsBought.idanimal) AnimalsBought
from AnimalsBought, Animals
where AnimalsBought.idanimal = Animals.idanimal
group by Animals.owner
) a
on a.owner = Producers.nif
left outer join ( select Animals.owner, count(Animals.idanimal) AnimalsExploration
from AnimalsExploration, Animals
where AnimalsExploration.idanimal = Animals.idanimal
group by Animals.owner
) b
on b.owner = Producers.nif;
(It is this type of query that I tested the performance of below).
Rather than bloat this answer with information probably not of interest to the OP, my notes on the relative performance of scalar subqueries and inline views in Oracle (requested by PerformanceDBA) are now offline here: Notes on Performance
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…