Database join struggle

[Russell Abbott]

I'm working on a DB join and am struggling.

On the Incident table, I have a 'cmdb_ci' field, for users to select a device name

On a custom table, I just have a list of device names that are updated every Sunday

I'm trying to view all Incidents where the cmdb_ci.name is one of the names on my custom table

I have added INC (incident) and CID (custom table) with the where clause set to 'inc_cmdb_ci = cid_u_name'

With no luck. Do i need to add the cmdb_ci table here as a 3rd table?

Any help would be greatly appreciated

[Omkar Mone · Accepted solution]

The inc_cmdb_ci field will return a sys_id, whereas the cid_u_name field contains the name of the CI, not the sys_id.

To resolve this, you'll need to add another table (e.g., the cmdb_ci table) to match the CI's sys_id with its corresponding name. Then, use the name field from the cmdb_ci table to join with the cid_u_name field in the custom table.

[Russell Abbott]

That seems to have worked. Thanks!

[Ankur Bawiskar]

@Russell Abbott 

Is that field on custom table a list field holding multiple values?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Russell Abbott 

On the custom table the field is of type list referring to cmdb_ci?

if yes then there is no direct way to handle the where clause for list field

check this link for workaround

Database view with list field 

If my response helped please mark it correct and close the thread so that it benefits future readers.