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 don't see any responses but did you ever figure this out.  I'm running in a similar problem with a database view.

[Shiraz2]

Please see the attached document. I hope this guides you.

If helps, please mark accordingly.

Shiraz.

[Brian Lancaster]

This looks like creating a database view outside of ServiceNow which is not what I'm doing.

[Oleg]

The only workaround, which I found was removing of condition like t.u_company_type='Vendor' from the database view and usage of condition like t_u_company_type='Vendor' every time, when the database view need be used. It's very uncomfortable, but it works at least.