How to create database view

[runfast]

I am working on creating a database view by joining CMDB_CI table with CMDB_REL_CI table. I need to CMDB_CI table where class is "cmdb_ci_service" and find a matching "cmdb_ci_service" under the "cmdb_rel_ci" table by matching CMDB_CI.sys_id with cmdb_rel_ci.child (sys_id) (if there is no match still output the cmdb_ci_service (business service name).

Here is how I would create my query in SQL:

SELECT * FROM cmdb_ci as ci

LEFT JOIN cmdb_rel_ci as rel

on ci.sys_id = rel.child

where ci.sys_class_name='cmdb_ci_service'

Here is what i have for the database view:

This database view returns the business services from CMDB_CI table which has a matching records under the relationships table only. The desired outcome is to return all the business services in CMDB_CI table regardless of matching records under the relationships table. (Left join)

I also tried to move the where clause from CMDB_ci table to the relationship table and these returns all CMDB_CI classes without the filter.

Any directions or guidance greatly appreciated.

thanks

[Bert_c1]

Try moving the Left Join to the cmdb_rel_ci table, remove it from the cmdb_ci.  And you were correct to move the where clause to the table owth order 200, as when the table at order 100 is "processed" it has no idea what the 'rel' prefix applies to.

Check OOB examples in your instance.

[runfast]

Thanks for looking at this for me!

I did try that, moved the where clause to the rel table and changed the left join on the rel table to TRUE (CMDB CI to FALSE), but now it is timing out. probably attempting to return lots of records.

[Bert_c1]

the cmdb_rel_ci table is much smaller than the cmdb_ci table, and querying cmdb_ci can timeout.  Maybe use the where clause: "ci.sys_class_name='cmdb_ci_service'" to the cmdb_ci table, and remove that part from the where clause on cmdb_rel_ci. I will try the same when I get time.

[Bert_c1]

Hi,

I defined:

Maybe what you want.  Set the view fields as desired from the two tables. for me, those records in cmdb_rel_ci that have no child show.