How to find "NOT IN" records in tables

[Bala19]

Hello All,

 I have 2 tables. Lets say Table A and Table B. Need to find the records available in Table A which is not available Table B through some join query using scripts without looping. What could be the best way to do it? Thank you in advance

thanks

Bala

[Murthy Ch]

Hi @Bala19 

You can use the sample logic below:

var tabA=new GlideRecord("table_nameA");
tabA.query();
while(tabA.next())
{
var tabB=new GlideRecord("table_nameB");
tabB.addQuery("applicationfield_name", tabA.applicationfield_name);
tabB.query();
if(!tabB.hasNext())
{
gs.info("This application" + tabA.applicationfield_name +" is not present in Table B");
}
}

Hope it helps

[Bala19]

Thanks Murthy. But this hit again table for each application. so I think array is best way if no option available through query

[Murthy Ch]

Hi @Bala19 

Yes. Then you can use below script in such case:

var arrA=[];
var arrB=[];
var tabA=new GlideRecord("table_nameA");
tabA.query();
while(tabA.next())
{
arrA.push(tabA.applicationfield_name.toString());
}
var tabB=new GlideRecord("table_nameB");
tabB.query();
while(tabB.next())
{
arrB.push(tabB.applicationfield_name.toString());
}
var res=new global.ArrayUtil().diff(arrA, arrB);
gs.info("These application's are not available in tableB but available in tableA " +res);

Hope it helps