Finding How Tables are Related

[jmiskey]

We need to create a database view so that we can ultimately see all assets that do not have any user entitlements associated with them.   Since this involves multiple tables, I believe that I need to create a Database View,

Using the Database Schema maps, it looks like I need to go from the Asset table (alm_asset) to the Software License table (alm_license) to the User Entitlement table (alm_entitlement_user) table.   However, is there a way to see exactly how these tables are connected?   Specifically, which fields are they joined on (we need to know that to connect them in the Database View)?

I could mess around with trial and error, but I figure that there has got to be a better way.   I actually tried connecting the Asset table and Software License table on the "asset_tag" field, since both have that field in them, but that tossed a bunch of errors back to me.   So I figured that cannot be correct and there has got to be a better way of finding the appropriate linking fields.

Thanks

[Jaspal Singh]

Hi Joe,

Look for System definition >> Tables & Colums & then select any table & you will have option of schema map to view relation amongst tables.

[jmiskey]

I already did that, and can see that the tables are related, but do not see how to tell by which fields they are related.   How do I do that?

[SudhirOjha]

if you click on columns and the text against the column name is written as referenced to which table . this way you can find the links.

[jmiskey]

I am sorry, but I am not seeing it.   Maybe it is there and I am just not looking in the correct place.

How does that show me which fields that the tables are linked on?

In creating a Database View, you have to explicitly tell it which fields to join the tables on.   That is what I am trying to figure out.

So, if you look at section 2.2 here: http://wiki.servicenow.com/index.php?title=Database_Views#gsc.tab=0

I am trying to build that Where clause, which in that example looks like:

mi_id=inc_sys_id

So it links the id field from the one table to the sys_id table of the other field.

I am trying to figure out from the Schema map, how exactly to find which fields I need to link the two tables.

In my example of linking alm_assets to alm_license, how can I find exactly which fields to use to create that join for that Where clause?