Search code examples
sqldatabaseoraclenorthwind

Query returns cartesian product (Northwind)


enter image description here

I am using northwind database in oracle.

Task : Fetch no. of employees working in each region.

Result: (RegionName, No. of employees)

I am trying this query but it return cartesian product

select r.regiondescription, count(e.employeeid)
from employees e,
     employeeterritories et,
     territories t,
     region r 
where r.regionid = t.regionid
  and et.territoryid = t.territoryid
  and e.employeeid = et.employeeid
group by r.regiondescription;

Question: What is wrong with my query?


Solution

  • The only thing I can think of, is that one of your table is multiplying results, and you should use count(DISTINCT) instead:

    select r.regiondescription, count(distinct e.employeeid)
    from employees e,
         employeeterritories et,
         territories t,
         region r 
    where r.regionid = t.regionid
      and et.territoryid = t.territoryid
      and e.employeeid = et.employeeid
    group by r.regiondescription;