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

[runfast]

Based on your setup (screenshot) you mean the records in cmdb_ci table that have no child show? because, what i need is to show me business service records (cmdb_ci_service) in CMDB_CI table regardless of a relationship exist in cmdb_rel_ci table child record. We have business services in the cmdb_ci table which don't have relationships in cmdb_rel_ci table.

hope this makes sense.

[Bert_c1]

Hi,

I meant that records in cmdb_rel_ci with *NO* child value show. And I forgot to add the condition for sys_class_name = 'cmdb_ci_service' to the where clause for the cmdb_ci table.

I now get 37 records in my PDI.  And if I change the cmdb_ci to cmdb_ci_service, I eliminate the sys_class_name filter and get the same results. See:

One more point, "_" should be used in where clauses in place of ".". See OOB database views. I updated mine and get the same results.

[runfast]

I really appreciate you for taking the time to help on this. 

here is the exact scenario for the database view:

1. Add a new business service under the CMDB_CI_SERVICE table without any relationship. "Servicewithout Relationship"
2. Add a new service called "Service withoutRelationship-1" under the CMDB_CI_Service table without a relationship to another business service. 
3. Add a new service called "ServicewithoutRelationship-2" under the CMDB_CI_Service table with a relationship to "Service without Relationship-1".

Now we have three new services under the cmdb_ci_services, one without any relationships and two with relationships.

So CMDB_CI TABLE (configuration item) should have these three CIs

CMDB_REL_CI table should have these 1 record:

The desired database view should have these records;

cmdb_ci.Name	cmdb_rel_ci.child.name
Service without Relationship
Service with Relationship2
Service with Relationship1	Service with Relationship2

[Bert_c1]

Hi,

I am a little confused, there is the cmdb_ci_service table that is a child of cmdb_ci. And you indicate your are creating 'services' in cmdb_ci. And there is not corresponding records in the cmdb_rel_ci table (for "Service with Relationship2")? I don't have the data to test with.

Try to formulate the desired SQL query, and replicate that in a database view.  You can enable "Debug SQL" and run your database view, to see what the result is.

I hope find what you're looking for.