sql - How to turn 2 queries with common columns (A, B) and (A, C) into just one (A, B, C)?

Question

I currently have 2 queries that return

PRODUCER                       FirstQueryColumn       
------------------------------ ---------------------- 
aaaaaaaaaaaa                   1                      
bbbbbbbbbbb                    1                      

PRODUCER                       SecondQueryColumn      
------------------------------ ---------------------- 
aaaaaaaaaaaa                   2                      
bbbbbbbbbbb                    1                      

What I'd like to know is how should I go about making it such that I can have in a single query the same data, that is, I want something that'll yield (Producer, FirstQueryColumn, SecondQueryColumn).

How can I do that?

Here are my current queries:

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;

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;

As you can see, for this case a join will not do much:

select Producers.name Prod, count(AnimalsBought.idanimal) AnimalsBought, count(AnimalsExploration.idanimal) AnimalsExploration
from Producers, Animals, AnimalsBought, AnimalsExploration
where (AnimalsExploration.idanimal = Animals.idanimal) and (Animals.owner = Producers.nif) group by Producers.name;

or am I doing something wrong?

Answer 1

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