Jump to content

Link multiple tables (4 tables) in a report


Joyce
 Share

Recommended Posts

Hi,

 

I need to link table 4 tables:   h_itsm_requests, h_itsm_questions, h_cmdb_links,  and h_cmdb_assets_computer.

 

The field h_itsm_requests.h_pk_reference is equals to "h_itsm_questions.reference" , h_cmdb_links.h_fk_id_l is like "h_itsm_requests.h_pk_reference" and 

h_cmdb_links.h_fk_id_r is like  "h_cmdb_assets_computer.h_pk_asset_id"

How can I correctly join them in the report ? currently have them as per below, and I am getting an error, also the last join, seems to pick up the middle join

image.png.ee66129a81dc2891444a7bc1774d2c92.png

image.png.19c3771d8f587bacf2baff3e0d8a5bac.png

image.png.670da1396cbe9b70fc3937a2c52f3fb2.png

Link to comment
Share on other sites

@Joyce to make this work properly you would need to join 5 tables actually (+h_cmdb_assets). Here are the JOIN conditions for all tables:

 

h_itsm_questions joined to h_itsm_requests:

image.png

 

h_cmdb_links joined to h_itsm_requests:

image.png

 

h_cmdb_assets joined to h_cmdb_links:

image.png

 

h_cmdb_assets_computer joined to h_cmdb_assets:

image.png

 

Here is the definition file for the report as well in case you want to have a look at it: link-4-tables-for-joyce.report.txt

 

As a side note, it would be a good idea to avoid using LIKE operator in JOINs because they are very ineffective and can bring much pain to the database ... some inefficient queries can bring down the database service to a halt, so keep this in mind ;) 

Link to comment
Share on other sites

@Joyce - well, you never told me what you need this report for... what information you need to get from it... so I just built the JOINs...  However it might be the case that you need to use LEFT JOIN or RIGHT JOIN on some JOINs depending on  what records you need returned...so, tell me what records and what information you need returned and I'll tell you how you need to have these JOINs...

Link to comment
Share on other sites

Hi @Victor,

the field that I need are:

h_itsm_requests.h_pk_reference    
h_itsm_requests.h_fk_user_name    
h_itsm_requests.h_dateresolved     
h_sys_accounts. h_email    
h_itsm_requests.h_custom_c    
h_itsm_requests.h_catalog    
h_cmdb_assets_computer.h_model    
h_itsm_requests.h_custom_c    
h_cmdb_assets_computer.h_name

Filter by h_itsm_questions-> FormID = "Order" And h_itsm_questions-> Question Value = "Docking Station" AND h_itsm_questions-> QuestionID = "field_4"

 

Thanks

Joyce

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