Database view left join isn't working

[TD14]

Hi All,

I have two tables: First is "core_company" which you all probably know, and a custom table "u_lp_vendor_contact".

For table "core_company" I added a custom field "u_company_type" with a value of "Vendor".

Table "u_lp_vendor_contact" has a reference field to core_company and represents contacts of vendors.

I am trying to build a database view that will present records from "core_company" table that don't have reference field in table "u_lp_vendor_contact".

When I run the database view with a left join=true, It ignores the condition of "u_company_type=Vendor", and even when I filter the results manually (u_company_type=Vendor), it doesn't return the right result.

The expected results when I run the query should be: "Unknown Vendor", "Vendor B", "Vendor C", "Equinix"

Attached: my database view, table core_company records, table u_lp_vendor_contact records, the result of database view run.

Please suggest what am I doing wrong.

Thanks

[Brian Lancaster]

I got the answer from my own post.  Looks like I just needed to adjust the order of the parent table to be a lower number than the child table.

[Oleg]

Thanks for the info. I used the order too, but one gets problems if one would need to add more conditions in join. For example t.u_company_type='Vendor' could break database view. I found that some conditions do work, but another don't work. In the case I know only one workaround - moving condition from JOIN to condition applied to the database view. In any way thanks for the info.

[Brian Lancaster]

In a database you would never use dot (.) in the condition.  Everything should be underscores (_).

[Maicol]

In database view use "_" instead "." and "&&" instead "and".

example:

t_sys_id=b_u_vendor && t_u_company_type...(correct)

instead

t.sys_id=b.u_vendor and t.u_company_type...

Some other recomendations:
https://docs.servicenow.com/bundle/orlando-platform-administration/page/use/reporting/task/t_AddATableToTheDatabaseView.html

[Bjoiern Hesse]

I have the same scenario, you have to join core_company against core company by sys_id to be able to add filters on that result. Steps. 

* first you LEFT join company with contact -> gives you the big set

* then you join core_company (1st row) again itself! and filter on that in the 3rd row

* the 4th row is just in addition for me to replace somehow (in this case dirty) the missing LIKE statement.