SQL query returning cartesian product

Question

I have some tables:

Employee: id, name, id_suc, id dep, id_sec
Suc     : id_suc, name
Dep     : id_dep, id_suc, name
Sec     : id_sec, id_dep, id_suc, name

Don't blame on me, this is an existing application, I didn't create the Database and can't touch the structure since there is too much data inside and reports depending on it. I'm just trying to modify a report as asked.

I do a query:

SELECT DISTINCT 
  s.name as sucurs, 
  d.name as depart, 
  c.name as section, 
  e.name AS emp 
FROM 
  employee e 
  join suc s on (e.id_suc = s.id_suc) 
  join dep d on (e.id_dep = d.id_dep) 
  join sec c on (e.id_sec = c.id_sec) 
ORDER BY 
  sucurs, depart, section, emp

and brings me a cartesian product. I want:

sucurs1, depart1, section1, emp1
sucurs1, depart1, section1, emp2
.....

(then on the report I group by suc, then dep, then sec)

instead, I got:

sucurs1, depart1, section1, emp1
sucurs2, depart1, section1, emp1

and so on. It brings ALL sucurs, ALL depart, ALL section and sometimes duplicated emp.

I'm missing something, but don't know what. Any clues?

Answer 1

Well, you're always joining the tables to only employee - it would seem that Dep is also linked to Suc - so you need a second JOIN condition (join not only on id_dep but also on id_suc!). Table Sec even needs three JOIN conditions since it shares three id's with the Employee table.

SELECT DISTINCT 
  s.name as sucurs, 
  d.name as depart, 
  c.name as section, 
  e.name AS emp 
FROM 
  employee e 
INNER JOIN 
  suc s ON e.id_suc = s.id_suc
INNER JOIN 
  dep d ON e.id_dep = d.id_dep AND e.id_suc = d.id_suc
INNER JOIN 
  sec c ON e.id_sec = c.id_sec AND e.id_suc = c.id_suc AND e.id_dep = c.id_dep
ORDER BY 
  sucurs, depart, section, emp