Help with Database View Where Clause

[Le Son]

Currently I have 2 tables. One contains the IP Address. One contains IP Address Copy.

I want create a database view display all 'IP Address' no have on table contains 'IP Address Copy'.

So how to write 'when clause'.

I created database view, Details are in the picture below

[Brad Bowman · Accepted solution]

Unless I'm mis-understanding what you want to achieve, you don't need to join the 2 tables, so just setup your view on the regist table, with no Where clause and no entry for the copy table.  You will then create a before Query Business Rule, using the view name as the Table, which will give you a list view of this table that shows records which have an IP Address which does not appear anywhere in the copy table.  By creating a Database View and Business Rule, you're not affecting the regular list view of this table.  The script for the Business Rule will look something like this:

(function executeRule(current, previous /*null when async*/) {
    var recArr = [];
			
    //add all records from this table that have an IP address which isn't in the copy table
    var recGR = new GlideRecord('x_841544_regist_checkduplicateip');
    recGR.addQuery('ip_regist', '!=', ''); //exclude blank IP Address records
    recGR.query();
    while (recGR.next()) {
        var copyGR = new GlideRecord('x_841544_regist_registcopy');
        copyGR.addQuery('ip_regist, recGR.ip_regist); // IP Address on copy table matches IP Adress of this record on first table
        copyGR.query();
        if (!copyGR.next()) { // record-match not found, so show this record in the filtered view
            recArr.push(recGR.ipaddress_sys_id.toString()); //table prefix used in database view
        }
    }
    current.addQuery('ipaddress_sys_id', 'IN', recArr.join(','));
})(current, previous);