Re: [SQL] I'm stumped

[Iain]

Hi,
I didn't bother analysing this too deeply, so keep that in mind when you 
read my reply ;-)

However the point that set off alarm bells for me was this statement "we now 
have an over-ride table".

It seems to me that you shouldn't be over-riding anything, and the emp_dept 
table should just be history -  ie "emp_dept_his", and it wouldn't need the 
"active" column.

If you need history then you could use a trigger or rule to insert the 
history record whenever the emloyee changes department.

The key question here is "Why wouldn't you change the key in the emp table 
when that key represents the employee's department?". It seems like a 
handbook case to me but maybe I missed something.

regards
Iain
I can't figure out an efficient way to do this. Basically I had a typical 
3-tier relationship:
(Employee -> Department -> Division)
However, at some point the need to move employees arose, but instead of 
changing the key in the emp table, we now have an over-ride table, so a 
history can be tracked.

If I want to get the info for a particular employee, its a pretty simple 
deal, however, getting all the employees for a dept or division has 
become troublesome.

A very simplified schema:
divisions ( div_id, div_name );
departments ( dept_id, dept_name, div_id );
employees ( emp_id, emp_name, dept_id );
emp_dept ( emp_id, dept_id, active, changed_by, changed_when );
The original way that worked well:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
  FROM divisions v
       INNER JOIN departments d
               ON d.div_id = v.div_id
       INNER JOIN employees e
               ON e.dept_id = d.dept_id
 WHERE v.div_id = 123;
What was initially tried:
SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name
  FROM divisions v
       INNER JOIN departments d
               ON d.div_id = v.div_id
       INNER JOIN employees e
               ON e.dept_id = d.dept_id
       LEFT JOIN emp_dept ed
              ON ed.emp_id = e.emp_id AND ed.active = true
 WHERE v.div_id = 123;
This query is flawed, as it still always puts the employees in their 
original div, but reports the new dept. Which we didn't catch as a 
problem until emps were moved to depts in a different division.

I tried creating a function:
CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS '
SELECT CASE WHEN ed.dept_id IS NOT NULL
            THEN ed.dept_id
            ELSE e.dept_id END
  FROM employees AS e
       LEFT JOIN emp_dept AS ed
              ON ed.emp_id = e.emp_id AND ed.active = true
 WHERE e.emp_id = $1
' LANGUAGE SQL STABLE;
And then tried:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
  FROM divisions v
       INNER JOIN departments d
               ON d.div_id = v.div_id
       INNER JOIN employees e
               ON get_empdept(e.emp_id) = d.dept_id
 WHERE v.div_id = 123;
However since the function is not immutable (since it does a query), I 
can't create an index, and the join always does a seq scan.

I also thought to create a view, but I don't believe Postgres supports 
indexed views. It was always using a seq scan too.

The above examples are actually quite simplified, as several other tables 
get joined along the way, I'm not sure a UNION would work or not, how 
would it exclude the ones that match the dept_id in the emp table for 
those emps that match on the over-ride table?

Any suggestions?
Hello,
have you an index on emp_dept on emp_id, dept_id ?
what about this ?
SELECT    v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM    divisions v
   INNER JOIN departments d ON d.div_id = v.div_id
   INNER JOIN employees e ON e.dept_id = d.dept_id
WHERE    NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id)
AND    v.div_id = 2
UNION ALL
SELECT    v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM    divisions v
   INNER JOIN departments d ON d.div_id = v.div_id
   INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id
   INNER JOIN employees e ON e.emp_id = ed.emp_id
WHERE    ed.active=true
AND    v.div_id = 2
Regards,
Thomas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]