none
Unable to make use of INNER JOIN as expected.

    Question

  • Hello Folks,

    I was trying with below query to find ResourceID and Loaded user where their lastusetime is latest and refcount is highest.

    select distinct a.ResourceID, Replace(a.LocalPath0,'C:\Users\','') AS LoadedUser
    from v_GS_USER_PROFILE a INNER JOIN v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0 INNER JOIN v_R_System c
    ON a.ResourceID = c.ResourceID INNER JOIN (select distinct a.ResourceID, max(a.LastUseTime0) AS maxdate
    from v_GS_USER_PROFILE a INNER JOIN v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    WHERE a.Loaded0 = '1'
    AND Replace(a.LocalPath0,'C:\Users\','') IN (select b.User_Name0 from v_R_User b) GROUP BY a.resourceid) e
    ON a.ResourceID = e.ResourceID
    INNER JOIN (select distinct a.ResourceID, max(a.RefCount0) AS maxrefcount
    from v_GS_USER_PROFILE a INNER JOIN v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    WHERE a.Loaded0 = '1'
    AND Replace(a.LocalPath0,'C:\Users\','') IN (select b.User_Name0 from v_R_User b) GROUP BY a.resourceid) f
    ON e.ResourceID = f.ResourceID
    WHERE a.Loaded0 = '1' AND Replace(a.LocalPath0,'C:\Users\','') IN (select b.User_Name0 from v_R_User b)
    AND a.RefCount0 != 0 AND a.RefCount0 IS NOT NULL

    But somehow I am unable to get this as still I am seeing ResourceID and Loadeduser against all refcount for ResourceID instead of getting ResourceID and Loadeduser only for highest refcount for each loadeduser.

    Any idea around this?

    Friday, April 21, 2017 2:03 PM