none
Return data even if one of the SELECT values isn't matched.

    Question

  • Greetings,

    First let me explain that while I can get around Windows PowerShell, I am neophyte when it comes to SQL queries and while I am trying to read as much as I can my head is spinning from all of the different ways to do something.

    That being said, I have written a Lync User last logon report PowerShell script that queries all of the SQL databases on our Lync Front-End servers for user last logon informaiton:

    Generate Lync User Last Logn Report

    Originally I used the following SELECt statement against each Front-End server to gather all of the user logon information which worked great:

    SELECT
    	HRDTBL.LastNewRegisterTime,
    	RESTBL.UserAtHost,
    	CONVERT(VARCHAR(100),RESDIRTBL.AdDisplayName) AS AdDisplayName
    FROM
    	rtc.dbo.Resource RESTBL,
    	rtc.dbo.ResourceDirectory RESDIRTBL,
    	rtcdyn.dbo.HomedResourceDynamic HRDTBL
    WHERE
    	HRDTBL.OwnerId = RESTBL.ResourceId AND
    	HRDTBL.OwnerId = RESDIRTBL.ResourceId

    This returns approximately 7000 results across all of our Front-End servers with dates going back as far as February 2012.

    However I tried to get fancy and include the client application type/version with the information in the following adjusted query:

    SELECT
    	HRDTBL.LastNewRegisterTime,
    	RESTBL.UserAtHost,
    	CONVERT(VARCHAR(100),RESDIRTBL.AdDisplayName) AS AdDisplayName,
    	CONVERT(VARCHAR(100),REPTBL.ClientApp) As ClientApp
    FROM
    	rtc.dbo.Resource RESTBL,
    	rtc.dbo.ResourceDirectory RESDIRTBL,
    	rtcdyn.dbo.HomedResourceDynamic HRDTBL,
    	rtcdyn.dbo.RegistrarEndpoint REPTBL
    WHERE
    	HRDTBL.OwnerId = RESTBL.ResourceId AND
    	HRDTBL.OwnerId = RESDIRTBL.ResourceId AND
    	HRDTBL.OwnerId = REPTBL.OwnerId

    That dropped the results down to less than 2000, with no dates older than August 2013, presumably because the older logons don't have the corresponding client information so they don't match the WHERE requirement.

    I would like some assistance/direction in modifying the 2nd query above so that it returns all 7000 entries even if there isn't a match on the client application type/version information (where the value would just be blank for that column).

    I have a programmers background and I have been reading up on nested SELECT statements, JOINS (inner, outer, left, right), and countless other "I don't know what I am doing but this sounds like it might work" solutions to the point where my head is spinning. Can someone please suggest a code modification that will allow me get the data query I desire?

    Thanks!

    Wednesday, October 02, 2013 2:33 PM

Answers

  • Without knowing those tables: You're using INNER JOINs with the pre-SQL-92 syntax. Your query is equal to

    SELECT  HRD.LastNewRegisterTime ,
            R.UserAtHost ,
            CONVERT(VARCHAR(100), RD.AdDisplayName) AS AdDisplayName ,
            CONVERT(VARCHAR(100), RE.ClientApp) AS ClientApp
    FROM    rtcdyn.dbo.HomedResourceDynamic HRD
            INNER JOIN rtc.dbo.ResourceDirectory RD ON HRD.OwnerId = RD.ResourceId
            INNER JOIN rtc.dbo.[Resource] R ON HRD.OwnerId = R.ResourceId
            INNER JOIN rtcdyn.dbo.RegistrarEndpoint RE ON HRD.OwnerId = RE.OwnerId;

    But you need at least one LEFT JOIN, you need to test which one:

    SELECT  HRD.LastNewRegisterTime ,
            R.UserAtHost ,
            CONVERT(VARCHAR(100), RD.AdDisplayName) AS AdDisplayName ,
            CONVERT(VARCHAR(100), RE.ClientApp) AS ClientApp
    FROM    rtcdyn.dbo.HomedResourceDynamic HRD
            LEFT JOIN rtc.dbo.ResourceDirectory RD ON HRD.OwnerId = RD.ResourceId
            LEFT JOIN rtc.dbo.[Resource] R ON HRD.OwnerId = R.ResourceId
            LEFT JOIN rtcdyn.dbo.RegistrarEndpoint RE ON HRD.OwnerId = RE.OwnerId;



    p.s. using table alias names which are that long, don't help that much..
    Wednesday, October 02, 2013 2:41 PM

All replies

  • Without knowing those tables: You're using INNER JOINs with the pre-SQL-92 syntax. Your query is equal to

    SELECT  HRD.LastNewRegisterTime ,
            R.UserAtHost ,
            CONVERT(VARCHAR(100), RD.AdDisplayName) AS AdDisplayName ,
            CONVERT(VARCHAR(100), RE.ClientApp) AS ClientApp
    FROM    rtcdyn.dbo.HomedResourceDynamic HRD
            INNER JOIN rtc.dbo.ResourceDirectory RD ON HRD.OwnerId = RD.ResourceId
            INNER JOIN rtc.dbo.[Resource] R ON HRD.OwnerId = R.ResourceId
            INNER JOIN rtcdyn.dbo.RegistrarEndpoint RE ON HRD.OwnerId = RE.OwnerId;

    But you need at least one LEFT JOIN, you need to test which one:

    SELECT  HRD.LastNewRegisterTime ,
            R.UserAtHost ,
            CONVERT(VARCHAR(100), RD.AdDisplayName) AS AdDisplayName ,
            CONVERT(VARCHAR(100), RE.ClientApp) AS ClientApp
    FROM    rtcdyn.dbo.HomedResourceDynamic HRD
            LEFT JOIN rtc.dbo.ResourceDirectory RD ON HRD.OwnerId = RD.ResourceId
            LEFT JOIN rtc.dbo.[Resource] R ON HRD.OwnerId = R.ResourceId
            LEFT JOIN rtcdyn.dbo.RegistrarEndpoint RE ON HRD.OwnerId = RE.OwnerId;



    p.s. using table alias names which are that long, don't help that much..
    Wednesday, October 02, 2013 2:41 PM
  • Thank you for explaining that I was using INNER JOINs and that I needed a LEFT JOIN To do what I wanted. I was able to then look up the difference and understand a little bit more about how this all works on top of your providing the answer so thank you 2x! Here is what I cam up with based upon your assistance and my testing:

    SELECT
    	HRDTBL.LastNewRegisterTime,
    	RESTBL.UserAtHost,
    	CONVERT(VARCHAR(100),RESDIRTBL.AdDisplayName) AS AdDisplayName,
    	CONVERT(VARCHAR(100),REPTBL.ClientApp) As ClientApp
    FROM
    	rtcdyn.dbo.HomedResourceDynamic HRDTBL
    	INNER JOIN rtc.dbo.Resource RESTBL ON HRDTBL.OwnerId = RESTBL.ResourceId
    	INNER JOIN rtc.dbo.ResourceDirectory RESDIRTBL ON HRDTBL.OwnerId = RESDIRTBL.ResourceId
    	LEFT JOIN rtcdyn.dbo.RegistrarEndpoint REPTBL ON HRDTBL.OwnerId = REPTBL.OwnerId;

    From I understand of how the two different joins work, I first want to grab every logon entry in HomeResourceDynamic table, but only if there is a corresponding AdDisplayName and SIP address (UserAtHost), otherwise the last logon entry is worthless so use INNER JOIN. Then... if there is a client app rcorded for the logon add that as a 4th column so use LEFT JOIN. If I misunderstand please correct me.

    As for the longer aliases, single or two letter aliases don't really help me as a person trying to learn SQL queries from scratch remember what I am looking at. It is my understanding (and please correct me if I am wrongt) that slightly longer alias doesn't hurt anything, so I prefer to use names like RESTBL versus "R" to remind myself that is the RESource TaBLe. I get on folks all the time for using single letter variables like $j to represent a collection of objects in PowerShell scripts, so using a shorter alias here just didn't seem right to me. :-)

    Thank you VERY muhc for your quick and informative help!


    Wednesday, October 02, 2013 5:07 PM
  • The INNER JOIN only returns at least one row, when both tables have a row which satisfy the JOIN condition. Thus when you have a INNER JOIN and selected columns from both tables, then all these columns will return data (ignoring NULL columns). 

    The LEFT JOIN always retuns rows. It takes all rows from the "left" table and looks for rows on the "right" side where they match the JOIN condition. Thus when you have a LEFT JOIN and selected columns from both tables, then the columns from the right table may be all NULL, cause there is no matching row.

    Wednesday, October 02, 2013 6:15 PM