SQL query returning a cartesian product
I have several 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 me, this is an already existing application, I have not created a database and cannot touch the structure because there is too much data and reports inside it depending on it. I am just trying to modify the report as asked.
I am making a request:
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 cartesian products. I want to:
sucurs1, depart1, section1, emp1 sucurs1, depart1, section1, emp2 .....
(then in the report I am grouped through suc, then dep, then sec)
instead I got:
sucurs1, depart1, section1, emp1 sucurs2, depart1, section1, emp1
etc. It brings ALL sucurs
, ALL depart
, ALL section
and is sometimes duplicated emp
.
I'm missing something, but I don't know what. Any hints?
source to share
Well, you always join tables only to employee
- seemingly Dep
also related to Suc
, so you need a second JOIN condition (join not only on id_dep
, but also on id_suc
!). The table Sec
even requires three JOIN conditions as it shares three identifiers with the table employee
.
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
source to share