SELECT row if certain field is NOT null. Else, select row with null field instead-CodePudding

I'm having a hard time solving my sql problem. Would like to select a row if a certain field(LOOKUP) is not null. Else, get the row with the null field instead. Please see table below: (PAYCODE must be unique)

PAYCODE	LOOKUP	ACCOUNT
201	null	720001
201	659057	999999
202	null	720002

The output must be:

PAYCODE	LOOKUP	ACCOUNT
201	659057	999999
202	null	720002

This looks so easy but I am new to sql and solving this for 2 days while searching for solutions but no luck.

CodePudding user response：

You could try the following logic:

SELECT *
FROM yourTable t1
WHERE LOOKUP IS NOT NULL OR
      NOT EXISTS (SELECT 1 FROM yourTable t2
                  WHERE t2.PAYCODE = t1.PAYCODE AND
                        t2.LOOKUP IS NOT NULL);

Demo

This logic retains any record whose LOOKUP is not null or any record for which there is no non null record having the same PAYCODE.