asked on

to find non matching records

Hi,
i want to find the request id which has diffrent centre for same request id

request id centre
1001 8214
1001
1001 8214

1002 601
1002
1002 701

so result should be null can be filtered

1002 601
1002 701

Member_2_2484401

This will get you pretty close:

select requestId,
       centre,
       count(*)
  from deleteme
 where centre is not null
 group by requestId,
          centre
having count(*) = 1

REQUESTID          CENTRE     COUNT ( * )
    1,002             701               1
    1,002             601               1

Open in new window

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_276102

The suggested approach would give the result shown as long as every mismatch resulted in exactly count(*)=1. It would include all requestId values that only appeared in single rows as well as those that had multiple rows but single occurrences of centre.

What version of DB2 are you running?

What result do you want if the data looks this way:

request id centre
1001 8214
1001
1001 8214

1002 601
1002 601
1002
1002 701

1003 5701

Tom

Tomas Helgi Johannsson

Hi!
This should give you what you need

select distinct requestId, centre,
from deleteme
where centre is not null

Regards,
Tomas Helgi