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..

[Srinivasa Rao]

Exactly i realized that left outer join is not available here, based on my filter it supposed to show only records exists in TableA, but somehow its showing wrong results.. i am not able to figure it out what's wrong in my join or filter?? i have attached my join along with the filters...

any help would be really appreciated...

Thanks,

Srini

[Valentina6]

Hi Srini,

I suggest to modify the order: keep the change request table with current order and modify the other to 200. Moreover, I have noticed in my developer instance that the 'source table' column is empty for records that refer to change request. The rest seems ok

You can test the condition by steps, test first the join condition (change table on the left: chg_sys_id=apr_sysapproval), then you can add the other part (source_table =...).

Regards,

Valentina

[Srinivasa Rao]

it didn't work even after updating the order ,

may be i will try explore analytics as Guy Yedwab suggested, is there anything that i need to enable to create Explore analytics reports in ServiceNow???

[Valentina6]

It should work in this way:

Regards,

Valentina

[Valentina6]

With the left join = true condition on the second row.

Regards,

Valentina