Creating a Database View w/ Relationship Table as Main Table

[alantpham]

I am working on creating a Database View that includes the following tables

cmdb_rel_ci (CI Relationships)

cmdb_ci_appl (Applications)

cmdb_ci_server (Servers)

My goal is to try to create a database view of servers and applications within the cmdb that are involved in any CI relationship whether as a "Child" or "Parent". The relationship table list all relationships and the parent/child of each relationship. An application or server can be either or could be a parent in one relationship and a child in another. Because of this, I want to ensure no duplicates are returned from the two tables.

I am mostly struggling on understanding what the "Where" clauses should probably look like when I join the Application and Server tables.

Any help/suggestions would be appreciated!

[Aaron Molenaar]

Hi, I am working on something similar and in my personal developer instance happened across the database view below that seems to do some of what you are looking for. As a novice to database views, pretty sure this doesn't deal with parent-child / child-parent potential relationships you describe, but you may need to just add another line for the CI relationships table that reverses the where clause to make the host the child?

Thought I'd put it out there in case it helps. Enjoy!

Aaron