I have two table named A and B in both table 6 and 10 records are inserted respectively, in both table one field is common. How can we compare the records f both table using that common field and found that records which are not in the table A.

[abhisingh]

I have two table named   A and B in both table 6 and 10 records are inserted respectively, in both table one field is common. How can we compare the records of both table using that common field and found that records which are not in the table A.

[Brian Dailey1]

Hi Abhi,

The easiest way for me is to write a quick fix script (I'm fond of Fix Scripts because you can do pretty much anything and run them on-demand... however, take care because you CAN DO pretty much anything )

This script uses the [task] and [incident] tables for a quick example because they share the 'sys_id' field (since [incident] extends [task]):

//Get all Tasks

var grTask = new GlideRecord('task');

grTask.query();

//Get all Incidents

var grInc = new GlideRecord('incident');

grInc.query();

//Get all Tasks which ARE also Incidents

var grMatch = new GlideRecord('task');

//SYNTAX: addJoinQuery(joinTable: string, primaryField, joinTableField) -> GlideQueryCondition

grMatch.addJoinQuery('incident', 'sys_id', 'sys_id');

grMatch.query();

gs.print("Task Records Found:" + grTask.getRowCount());

gs.print("Incident Records Found:" + grInc.getRowCount());

gs.print("Matching Records Found:" + grMatch.getRowCount());

var matchesFound = [];

while(grMatch.next()){

        matchesFound.push(grMatch.sys_id.toString());

}

var grNoMatch = new GlideRecord('task');

grNoMatch.addEncodedQuery('sys_idNOT IN' + matchesFound.join());

grNoMatch.query();

gs.print("Non-Matching Records Found:" + grNoMatch.getRowCount());

You would simply need to follow the logic for the grMatch gliderecord, substituting your tables A for 'incident' and B for 'task' if you want to limit it to those having records existing in A.   Essentially whichever table you use in your Join query will be the limiting table.   Make sure to substitute your field names appropriately.

Give that a try.

Thanks,

-Brian

Edit:   sorry, my initial reply was on finding the matches, but you wanted those that don't have a match.   To do so, include the statements in bold...

[abhisingh]

what i am looking for is-- in table A where 6 records entered, it's 1 record is not available in table B(where 10 records entered), so i want to find out that one record which is in Table A but not in Table B.

[sush_s]

Hi Brian.

I also have the same question with little difference.

my requirement is To develop a dashboard report where we need to compare the CI's and its attributes of two tables and get the output in CSV format. If there are any similar CI's found in those tables, then they should be represented/displayed in red color and if not similar they should be represented with no color.

can we do this with code?  

please help me out.

Thanks in advance for any help or advice!

[adiddigi]

One way....

var table1 = 'table1';

var table2 = 'table2';

var commonFieldName = 'commonFieldName';

var arr = [];

var gr = new GlideRecord(table2);

gr.query();

while(gr.next()){

  var gr1 = new GlideRecord(table1);

  gr1.addQuery(commonFieldName,gr.getValue(commonFieldName));

  gr1.query();

  if(gr1.getRowCount()     == 0 ){

  arr.push(gr1.getValue(sys_id));

  }

}

gs.log(arr);