i am trying to create a database view to show records from table a but not exists in table b using left join

[Srinivasa Rao]

Hi,

I have two tables with similar column names and I need to return records from the left table which are not found in the right table

I know how to write this in SQL Server but not familiar with ServiceNow Left Join any help would be really appreciated..

SELECT a.*
FROM TableA a

LEFT JOIN TableB b

ON b.col1 =a.col2

WHERE b.col1 is NULL

What i need in ServiceNow is

SELECT a.*
FROM Change_Request a

LEFT JOIN sysapproval_approver b

ON b.sysapproval =a.SYS_ID

WHERE b.sys_id is NULL

Please advise...

Thanks in advance..

[Madhu27]

To create a table with a left join:

1. From the Database View form, click New on the View Tables related list.
2. Configure the form and add the Left join field (a check box) to the form.
3. Click Save.
4. Complete the form and select the Left join check box.
   • Selecting Left join causes the left-hand table in the database view to display all records, even if the join condition does not find a matching record on the right-hand table. Select this check box for view tables that specify a Where clause. Selecting Left join for view tables without a Where clause does not affect the query.
   • Joined tables are ordered left to right from lowest to highest Order values.
5. Click Submit.
6. Personalize the View Tables related list to show the Left join column.

   The Left join field shows a value of true.

   

7. Click a record to view a table.

   The View Table form appears.

8. To add an OR to your where clause use ||.

   For example, to query all incidents related to RFCs OR all incidents that are the parent of a change request, use the following syntax:

   inc_rfc = chg_sys_id || chg_parent = inc_sys_id

   

[Srinivasa Rao]

This is giving me like inner join(matching records from both the tables)

I need left outer join, Records exists only in TableA but not in Table B

Thanks in advance..

[Srinivasa Rao]

[Valentina6]

Hi,

from what I know, left outer join is not available, but you can reach the desired result by:

1. Creating a database view, setting the left join option as true
2. Filtering results in a list or report, excluding records with a match in table b (table b columns filled in).
3. Remaining records exist only in TableA but not in Table B

Regards,

Valentina