Jump to content

Manager column not listed


Shamaila.Yousaf
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.  

image.png

Link to comment
Share on other sites

@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.png

(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:

image.png

 

Link to comment
Share on other sites

  • 2 weeks later...

@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

  • Thanks 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...