Re: [SQL] Extract created and last modified data

[Oliveiros d'Azevedo Cristina]

Howdy, Gordon.

This query is an attempt to replicate your items table with data just from 
changelog table.
Could you please try it out?

Best,
Oliveiros

SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, itm_date_modify
FROM
(
SELECT a.itm_id, a.usr_id as usr_id_create, create as itm_date_create
FROM  changelog  a
JOIN
(SELECT MIN(log_timestamp) as create, itm_id FROM changelog GROUP BY itm_id) b
ON (a.itm_id = b.itm_id
AND log_timestamp = create)
) subquery1
NATURAL JOIN
(
SELECT c.itm_id, usr_id as usr_id_modify, last_modified as itm_date_modify
FROM changelog c
JOIN (SELECT MAX(log_timestamp) as last_modified, itm_id FROM changelog GROUP 
BY itm_id) d
ON (d.itm_id = c.itm_id
AND c.log_timestamp = last_modified)
) subquery2





  I have a CMS with a table of items, and another table serving as a log 
  of operations performed on the items.  The revelent table structure is 
  as follows: 

  items 
  itm_id | usr_id_create | itm_date_create | usr_id_modify | 
  itm_date_modify | ..... 


  itm_id is a serial primary key. 


  usr_id_* are the keys of the users who created and last modified the 
  item. 


  item_date_* are timestamps for creation and last modification times. 


  changelog 
  itm_id | usr_id | log_timestamp | log_op 


  itm_id and usr_id are foreign keys into the respective item and user 
  tables.  log_timestamp is when the last change was made and log_op was 
  the operation performed. 


  . 


  Can anybody help with writing the query to get a view that replicates 
  the items table except with the create and last modified user IDs and 
  timestamps loaded from the changelog table?  If there is anybody who 
  has actually implemented something similar, what kind of performance 
  impact would it have?