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

Answers

  • Here you go

    WITH
    LOADEDUSERLASTUSETIME (ResourceID,LastUseTime0)
    AS (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),
    LOADEDUSERMAXREFCOUNT (ResourceID,RefCount0)
    AS (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)

    SELECT * FROM
    (
    SELECT  ROW_NUMBER() OVER (PARTITION BY a.ResourceID ORDER BY  a.RefCount0 DESC) rn, a.ResourceID, Replace(a.LocalPath0,'C:\Users\','') AS LoadedUser, a.RefCount0, a.LastUseTime0 AS LastUseTime
    FROM #v_GS_USER_PROFILE a
    INNER JOIN #v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    INNER JOIN LOADEDUSERLASTUSETIME e ON a.ResourceID = e.ResourceID
    INNER JOIN LOADEDUSERMAXREFCOUNT f ON a.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
    ----AND a.ResourceID = 67263624
    ) AS Der WHERE rn=1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Mudit Bahedia Wednesday, April 26, 2017 2:28 PM
    Wednesday, April 26, 2017 8:12 AM
    Answerer

All replies

  • Hi Mudit Bahedia,

    Instead of subquery, study to use common table expression (CTE) to make your query more readable and easier to maintain. As for the problem, based on my experience, you could take a look at R​OW_​NU​MB​ER first. For further discussion, please provide some sample data to better illustrate this issue.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 24, 2017 10:28 AM
    Moderator
  • Thank you Sam for your reply!

    I have now used CTE and posting query below with some example (filter with resource id so as to compare only 1 resource id result):

    WITH
    LOADEDUSERLASTUSETIME (ResourceID,LastUseTime0)
    AS (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),
    LOADEDUSERMAXREFCOUNT (ResourceID,RefCount0)
    AS (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)
    select distinct a.ResourceID, Replace(a.LocalPath0,'C:\Users\',''), a.RefCount0, a.LastUseTime0 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 LOADEDUSERLASTUSETIME e
    ON a.ResourceID = e.ResourceID
    INNER JOIN LOADEDUSERMAXREFCOUNT 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
    AND a.ResourceID = 67263624

    And the result I am getting is as below:

    ResourceID	(No column name)	RefCount0	LoadedUser
    67263624	user1	                   5	        4/18/2017
    67263624	user2	                   1	        4/18/2017
    

    Whereas I was expecting row with only user1 as it has max(refcount0)

    Kindly let me know your inputs.

    Tuesday, April 25, 2017 11:47 AM
  • Is that possible to provide us with sample data to be tested? Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, April 25, 2017 12:07 PM
    Answerer
  • Hi Uri,

    Thank you!

    Kindly find below sample data:

    v_GS_USER_PROFILE:
    -----------------------------

    ResourceID	LastUseTime0	Loaded0	LocalPath0	                              RefCount0
    67263624	18-Apr-17	1	C:\Users\user1	                              1
    67263624	18-Apr-17	1	C:\Users\user2	                              0
    67263624	18-Apr-17	1	C:\Users\user3	                              5
    67263624	18-Apr-17	1	C:\Windows\ServiceProfiles\NetworkService     NULL
    67263624	18-Apr-17	1	C:\Windows\ServiceProfiles\LocalService	      NULL
    67263624	18-Apr-17	1	C:\WINDOWS\system32\config\systemprofile      1
    67325914	25-Apr-17	1	C:\Users\user4	                              3
    67325914	25-Apr-17	1	C:\windows\ServiceProfiles\NetworkService     NULL
    67325914	25-Apr-17	1	C:\windows\ServiceProfiles\LocalService	      NULL
    67325914	25-Apr-17	1	C:\WINDOWS\system32\config\systemprofile      1
    67325914	25-Apr-17	1	C:\Users\user2	                              0

    v_R_User:
    ---------------------

    ResourceID	User_Name0
    2063647265	user3
    2063928522	user1
    2063833557	user2
    2063946814	user4

    I am running SQL Query as below:

    WITH
    LOADEDUSERLASTUSETIME (ResourceID,LastUseTime0)
    AS (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),
    LOADEDUSERMAXREFCOUNT (ResourceID,RefCount0)
    AS (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)
    SELECT DISTINCT a.ResourceID, Replace(a.LocalPath0,'C:\Users\','') AS LoadedUser, a.RefCount0, a.LastUseTime0 AS LastUseTime
    FROM v_GS_USER_PROFILE a
    INNER JOIN v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    INNER JOIN LOADEDUSERLASTUSETIME e ON a.ResourceID = e.ResourceID
    INNER JOIN LOADEDUSERMAXREFCOUNT f ON a.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
    AND a.ResourceID = 67263624

    I am expecting result as below from above query (With highest ref count and latest date):
    ------------------------------------------------------------------------

    ResourceID	LoadedUser	RefCount0	LastUseTime
    67263624	user3	        5	        18-Apr-17
    But instead, I am getting result as below:
    --------------------------------------------------
    ResourceID	LoadedUser	RefCount0	LastUseTime
    67263624	user3	        5	        18-Apr-17
    67263624	user1	        1	        18-Apr-17

    SQL Version being used: Microsoft SQL Server 2012 - 11.0.5582.0 (X64)

    Any help on this is greatly appreciated.



    Tuesday, April 25, 2017 4:38 PM
  • But there is also user2 for April 25 and resource id=67325914 , so latest date for two resources 67263624 and 67325914? JOIN is on username column?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 26, 2017 5:17 AM
    Answerer
  • Modified your query  (Removed DISTINCT clause and added TOP 1 clause)

    create table #v_GS_USER_PROFILE (ResourceID int,LastUseTime0 date, Loaded0 int,LocalPath0 varchar(100) ,RefCount0 int)

    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\Users\user1',1)
    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\Users\user2',0)
    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\Users\user3',5)
    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\Windows\ServiceProfiles\NetworkService',NULL)
    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\Windows\ServiceProfiles\LocalService',NULL)
    insert into #v_GS_USER_PROFILE values (67263624 ,'20170418',1,'C:\WINDOWS\system32\config\systemprofile',1)
    insert into #v_GS_USER_PROFILE values (67325914 ,'20170425',1,'C:\Users\user4',3)
    insert into #v_GS_USER_PROFILE values (67325914 ,'20170425',1,'C:\windows\ServiceProfiles\NetworkService',NULL)
    insert into #v_GS_USER_PROFILE values (67325914 ,'20170425',1,'C:\windows\ServiceProfiles\LocalService',NULL)
    insert into #v_GS_USER_PROFILE values (67325914 ,'20170425',1,'C:\WINDOWS\system32\config\systemprofile',1)
    insert into #v_GS_USER_PROFILE values (67325914 ,'20170425',1,'C:\Users\user2',0)

    create table #v_R_User (ResourceID int, User_Name0 varchar(20))

    insert into #v_R_User values(2063647265, 'user3')
    insert into #v_R_User values(2063928522, 'user1')
    insert into #v_R_User values(2063833557, 'user2')
    insert into #v_R_User values(2063946814, 'user4')


    WITH
    LOADEDUSERLASTUSETIME (ResourceID,LastUseTime0)
    AS (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),
    LOADEDUSERMAXREFCOUNT (ResourceID,RefCount0)
    AS (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)
    SELECT TOP 1  a.ResourceID, Replace(a.LocalPath0,'C:\Users\','') AS LoadedUser, a.RefCount0, a.LastUseTime0 AS LastUseTime
    FROM #v_GS_USER_PROFILE a
    INNER JOIN #v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    INNER JOIN LOADEDUSERLASTUSETIME e ON a.ResourceID = e.ResourceID
    INNER JOIN LOADEDUSERMAXREFCOUNT f ON a.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
    AND a.ResourceID = 67263624
    ORDER BY a.RefCount0 DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 26, 2017 5:21 AM
    Answerer
  • Hi Uri,

    Thanks a lot for the reply!
    Using TOP 1 will be only helpful when I use 1 ResourceId as filter. I have used it just for providing sample.

    In actual, I need results for 150000 ResourceIDs and hence TOP 1 will not be helpful in that case.

    As my intention with this query is to select only 1 userid against each ResourceID but I am unable to accomplish this.
    I am not sure if I am missing something in query or not used something properly.

    Thanks again for the reply.
    Could you please help with above scenario in consideration?

    Wednesday, April 26, 2017 8:01 AM
  • But there is also user2 for April 25 and resource id=67325914 , so latest date for two resources 67263624 and 67325914? JOIN is on username column?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Yes, join is on username column.
    As ResourceID in v_gs_user_profile is resource id for Computer and ResourceID in v_r_user is resource if for User.
    Hence even though column name is ResourceID in both tables, I haven't used it in Join.

    Wednesday, April 26, 2017 8:05 AM
  • Here you go

    WITH
    LOADEDUSERLASTUSETIME (ResourceID,LastUseTime0)
    AS (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),
    LOADEDUSERMAXREFCOUNT (ResourceID,RefCount0)
    AS (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)

    SELECT * FROM
    (
    SELECT  ROW_NUMBER() OVER (PARTITION BY a.ResourceID ORDER BY  a.RefCount0 DESC) rn, a.ResourceID, Replace(a.LocalPath0,'C:\Users\','') AS LoadedUser, a.RefCount0, a.LastUseTime0 AS LastUseTime
    FROM #v_GS_USER_PROFILE a
    INNER JOIN #v_R_User b ON Replace(a.LocalPath0,'C:\Users\','') = b.User_Name0
    INNER JOIN LOADEDUSERLASTUSETIME e ON a.ResourceID = e.ResourceID
    INNER JOIN LOADEDUSERMAXREFCOUNT f ON a.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
    ----AND a.ResourceID = 67263624
    ) AS Der WHERE rn=1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Mudit Bahedia Wednesday, April 26, 2017 2:28 PM
    Wednesday, April 26, 2017 8:12 AM
    Answerer
  • You're awesome Uri!
    Thanks!

    I think this is working. :)

    Wednesday, April 26, 2017 2:28 PM