Link multiple tables (4 tables) in a report

[Joyce]

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

[Joyce]

@Victor help?

[Victor]

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

 

h_cmdb_links joined to h_itsm_requests:

 

h_cmdb_assets joined to h_cmdb_links:

 

h_cmdb_assets_computer joined to h_cmdb_assets:

 

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  

[Joyce]

Hi @Victor

With the above settings, I seems to be missing some of the data. I have check the request, they all have similar field. The report definition for the two are:

Test request for Victor- Missing rows.txt

Test report for Victor- Correct.txt

[Joyce]

the table join seems to eliminate some data. I have try to review, and I can't figure out why

[Victor]

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

[Joyce]

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

[Victor]

@Joyce - ok then use LEFT JOIN instead on pure JOIN on all ... it should bring back all requests

[Joyce]

Hi @Victor, 

Great it works. Thanks.

 

Regards,

Joyce