Database view with a three table join

ayman_h · ‎03-08-2024

Hi,

I am trying to create a database view that will give me all the SLAs linked to Incidents, where the Incident doesn't have an Incident Task related to it.

I am struggling trying to do this using a join.

Does anyone have an idea how I can do this?

Thanks,

Ayman

-O- · ‎03-16-2024

I had some time finally to look into the problem and below you'll find how I solved this in my PDI.

This is how the definition of the database view looks like:

The where clause for table task_sla is:

not exists (select 1 from `task` inct where `inct`.`sys_class_name` = 'incident_task' and `inct`.`a_ref_1` = `inc`.`sys_id`)

The where clause for table incident is:

inc.sys_id = tsla.task

The key part of the solution is finding the storage alias for field incident on table incident_task - a_ref_1 in the where clause for table task_sla.

For this one should open table Storage Column Aliases (sys_storage_alias) and filter it down to where Table (table_name) is incident_task and Element name (element_name) is incident:

Whatever is in column Storage alias (storage_alias) - a_ref_1 in my PDI - should be used.

(B.t.w. all the back-ticks are not really necessary, not exists (select 1 from task inct where inct.sys_class_name = 'incident_task' and inct.a_ref_1 = inc.sys_id) works just fine).

View solution in original post

Sumanth16 · ‎03-08-2024

Hi @ayman_h ,

. Basically you need to build the relationship between 1st and 2nd table and then 2nd table with 3rd table. In my case 1st table was sys_user_role 2nd table was sys_user_has_role and 3rd one was sys_user.

If I could help you with your Query then, please hit the Thumb Icon and mark it as Correct !!

Thanks & Regards,

Sumanth Meda

ayman_h · ‎03-08-2024

Thanks @Sumanth16 and @AndersBGS for your quick response on this. I tried a simple normal left join between the three tables (see screenshot) but it doesn't seem to work. How do I ignore the Incidents with Incident task is what I am struggling to solve?

AshishKM · ‎03-08-2024

Hi @ayman_h ,

I tried with example and its working on my PDI.

Created Incident Task and mapped with an incident, that incident is not listing in DB View.

-Thanks,

AshishKM

Please mark this response as correct and helpful if it helps you can mark more that one reply as accepted solution

ayman_h · ‎03-08-2024

Thanks @AshishKM . I tried the same thing but I can see Incident Tasks appearing on the DB view 😞