CODEWITHSUNDEEP
sqloracle11g
tlano
tlano

Reputation: 45

SQL Join multiple tables, count, include 0 counts

I'm using Oracle Database 11g. Say I have 3 database tables. One contains information about pizza shops, one contains information on people, and one contains information on orders:

PIZZA_SHOP
ID|  NAME          |
1  |  Dominos
2  |  Pizza Hut
3  |  Papa Johns

PERSON
NAME  |  PIZZA_SHOP_FK
Jim       |  Dominos
Tod      |  Pizza Hut
Kelly     |  Dominos
Frank   |  Papa Johns

ORDERS
PERSON_FK  |  ORDER
Jim                   |  Pepperoni
Tod                   |  Cheese
Kelly                 |  Pepperoni
Jim                   |  Sausage
Tod                   |  Specialty

We want to omit the specialty pizza. From this table, we can see that 2 pepperoni and 1 sausage pizzas were ordered from Dominos, 1 cheese pizza was ordered from Pizza Hut, and nothing has been ordered from Papa Johns. What I want to be able to do is get the following output:

Dominos --- pepperoni --- 2
Dominos --- sausage --- 1
Dominos --- cheese --- 0
Pizza Hut --- pepperoni --- 0
Pizza Hut --- sausage --- 0
Pizza Hut --- cheese --- 1
Papa Johns--- pepperoni --- 0
Papa Johns--- sausage --- 0
Papa Johns--- cheese --- 0

What I've tried is something like this:
SELECT PS.NAME, O.ORDER, COUNT(O.ORDER)
FROM PIZZA_SHOP PS
FULL OUTER JOIN PERSON P
    ON P.PIZZA_SHOP_FK = PS.NAME
FULL OUTER JOIN ORDERS O
    ON O.PERSON_FK = P.NAME
WHERE O.ORDER IN ('Pepperoni', 'Cheese', 'Sausage')
GROUP_BY PS.NAME, PS.TYPE

But unfortunately, it does not retrieve any of the '0' counts. I've tried several other ways and am starting to run out of ideas. Can anybody help me with a solution?

Upvotes: 0

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The idea behind this type of query is to create all the rows using cross join and then use left join and group by to bring the data together that you want. This is a bit more complicated because you have these weird relationships between persons and orders, but it is still doable:

select ps.name, toppings.order, count(o.order)
from pizza_shop ps cross join
     (select 'Pepperoni' as order from dual union all
      select 'Cheese' from dual union all
      select 'Sausage' from dual
     ) toppings left join
     order o
     on o.order = toppings.order left join
     persons p
     on p.pizza_shop_fk = ps.name and
        p.name = o.person_fk
group by ps.name, toppings.order;

Upvotes: 4

Related Questions