Manager column not listed

[Shamaila.Yousaf]

I have a report where I have joined table CMDB Assets and h_sys_accounts, I need to pick up the 'manager' details.  This is not listed in the column, is it down as one of the attributes?

 

Would be useful to know which field to allow us to pickup the info above.

 

Thanks everyone.

 

Sxx

[Steve Giller]

A User's Manager is stored in the h_sys_user_profiles table (h_manager) so you'll need an additional join to retrieve that.

[Shamaila.Yousaf]

Thanks @Steve Giller, will join the additional table.

 

Regards

Sxx

[Shamaila.Yousaf]

Hi there,

 

I have joined the table (see attached) but the report appears with 0 records?

Not sure where I have gone wrong.

 

[James Ainsworth]

Hi Shamaila,

Your join statement looks like it is trying to match an Asset Type ID with the manager field.  The manager field won't hold asset type IDs so there won't be a match.  I'm not sure what the exact output of your report is going to be, but I'm assuming that you are going to want to do a join between the Used By on the assets and the User name or ID in the accounts table.  

[Victor]

@Shamaila.Yousaf there are a couple of things re the way that report was built.

1. LEFT JOIN

Is it the correct type of join for the result set you are after?

(image courtesy of https://www.w3schools.com/)

If you need only assets that are used by a user then you need JOIN, if you need all assets including the ones that are not used by a user then you need JEFT JOIN.

 

2. As @James Ainsworth advised, the join condition for user profiles is incorrect. You need to join the profiles with accounts. This is where you will need the LEFT JOIN because it is possible not users will have manager but we do want that asset (used by the user) in the result. Example:

 

[Shamaila.Yousaf]

@Victor thanks for the explanation on the above, it was very useful to know as I don't use much SQL.

I think i had the elements of other tables amended, when i tried your fix above the report presented few records that it should have.

After making amendments, along with your fix for the LEFT JOIN it now gives me all the records along with the manager details.

@James Ainsworth @Steve Gillerthanks for your input also.

 

Sxx