Database view between Change request and its affected Incident ,problem record.

[Community Alums]

Need help in creating database view for Change Request on Task table, showing only Incident and Problem records that are related to a Change record. 

Change request is mapped to incident and problem by rfc field in system. Its working but need help in below issues: Not sure if need to modify the where clause and Left join.

1. In result screenshot see returning requests other than change_requests as well with empty problem and incident number.

2. Also returns change requests which are doesn't have incident or problem mapped.

 3. I have to put filter everytime to remove the redundant data result.

Is there any way to acheive this via modifying the query. Any help is appreciated.Thanks.

Task query  for reference -task where  clause - tsk_sys_class_name='change_request'&&tsk_sys_id!=''

Result

[Brian Lancaster]

I think you need to get ride of the left join. I only use left joins if I want to see both the data that meets my where clause and data that doesn't. For example we have requests items that we call free form. Which is no tasks are created by the workflow and the fulfillers could create task. I wanted to be able to report on items that were the task did not exist so I had to use a left join.

[Bert_c1]

To add here, why have 'task' as the first table?  And just have 'change_request' as the first table.

[Tabassum22 · Accepted solution]

Hi @Community Alums ,

For your requirement the left join should not be true for all the tables. It is returning you basically the task table records.

The below might help you 

Which is showing the Change related to all the problem records and incidents which are also related to some problem. 

 Note: The incidents which are related to change are not visible in this view as we can't add a OR clause between two tables. So here all the 3 where clause needs to be true which removes the change records having only incidents attached. 

Instead of 'task' table you can also use 'change_request' table in that case no need to add the where clause.

Please mark it helpful and try to give it a thumbs up if the above helps. Please accept the solution.

Thanks & Regards

Tabassum Sultana