CODEWITHSUNDEEP
sqldatabaseoracle-databasenorthwind
Nafees
Nafees

Reputation: 1034

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?

Upvotes: 1

Views: 173

Answers (1)

sagi
sagi

Reputation: 40491

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;

Upvotes: 3

Related Questions