1 Reply Latest reply on May 26, 2014 1:40 PM by josh.richau

    query for document creation, title and user activity - join issue

    don840

      The underlying query results in multiple repeating rows for the same document, when I introduce the jivedw_document table. I think the join to jivedw_document table is not correct. Can someone help me with the join?

      select
        doc.creation_ts,
        doc.title,
      usr.firstname,
      usr.lastname,
        act_meta.activity_name,
      obj_meta.object_name
      , cntr.name
      ,count(distinct doc.document_id)

      from
      jivedw_activity_fact actfact,
      jivedw_activity_meta act_meta,
      jivedw_object_meta obj_meta,
      jivedw_object obj,
      jivedw_user usr
      , jivedw_document  doc
      , jivedw_container cntr
      where
      actfact.dw_container_id = cntr.dw_container_id
      AND doc.container_id = cntr.container_id
      AND actfact.user_id = usr.user_id
      AND actfact.activity_type = act_meta.activity_type
      AND actfact.direct_object_type = obj_meta.object_type
      AND actfact.direct_dw_object_id = obj.dw_object_id
      and act_meta.activity_type = '20' 
      and obj_meta.object_name = 'Document'
        group by
      usr.firstname,
      usr.lastname,
      act_meta.activity_name,
      obj_meta.object_name,
      cntr.name,
        doc.title
        , doc.creation_ts
        order by doc.creation_ts