asked on 5/17/2011

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

DB2

Last Comment

Tomas Helgi Johannsson

5/23/2011

Member_2_2484401

5/17/2011

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

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Member_2_276102

5/17/2011

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

5/23/2011

Hi!
This should give you what you need

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

Regards,
Tomas Helgi