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
and is sometimes duplicated
I'm missing something, but I don't know what. Any hints?
source to share
Well, you always join tables only to
also related to
, so you need a second JOIN condition (join not only on
, but also on
!). The table
even requires three JOIN conditions as it shares three identifiers with the 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
source to share