Show me a list of all Assets and, if there's a corresponding entry in Users, the user info. I want a list of records from table 1 (let's say Assets) joined against table 2 (let's say Users). Left Join tblADUsers On tblADUsers.Userdomain = tblCPlogoninfo.Domain And tblADUsers.Username = tblCPlogoninfo.UsernameĪs far as joins, a simple explanation of LEFT vs INNER: Left Join tblCPlogoninfo ON tblCPlogoninfo.AssetID = tblAssets.AssetID AND tblCPlogoninfo.logontime = LogonHistory.LastLogon TblCPlogoninfo.AssetID) AS LogonHistory ON LogonHistory.AssetID = tblAssets.AssetID Max(tblCPlogoninfo.logontime) AS LastLogon I had previously suggested joining tblADUsers against tblAssets, but since you're pulling the last user info from tblCPlogoninfo, I've adjusted the join to link against that. Doing a LEFT JOIN against that list ensures that you don't drop machines with no logon history from the result set. The inner SELECT pulls a list of just the most recent logon on each asset so you don't have to do grouping on the main body of the query. Left Join tblADUsers On tblADUsers.Userdomain = LogonHistory.Domain And tblADUsers.Username = LogonHistory.Username Inner Join On tsysIPLocations.LocationID = tblAssets.LocationID TblCPlogoninfo.Username) AS LogonHistory ON LogonHistory.AssetID = tblAssets.AssetID Max(tblCPlogoninfo.logontime) AS LastLogon, Left Join tblADUsers On tblADUsers.Userdomain = tblAssets.UserdomainĪnd tblADUsers.Username = tblAssets.Username Inner Join On tsysIPLocations.LocationID = TblCPlogoninfo.Username = tblAssets.Username Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID And Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Max(tblCPlogoninfo.logontime) As LastLogon,
0 kommentar(er)
