Prioritizing one row over another, if it exists

Question

I have a commission_rates table with the following data:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
4	custom	0.5	3	408
5	standard	0.08	3	NULL

A row where publisher_id is null means that this rate applies to all publishers. So, if my publisher ID was 10 and I wanted to see a result set of applicable commissions for that publisher, it would return rows 1,2,3 and 5 i.e. not row 4 because that row is a rate customization for publisher ID 408.

The idea is to avoid explicitly defining a rate for every merchant <> publisher relationship. The rates for each publisher <> merchant are 99% the same, but for a few customizations where a different rate was agreed between the two parties. So a null value in the publisher_id column means that this is the rate used for all publishers.

A row where publisher_id is not null means that this publisher has a customized rate for the merchant.

For example, row 4 features a custom rate for publisher 408. If we query for publisher 408, row 4 should be returned in place of the standard rate for all publishers (row 5).

So a query for publisher 408 would return:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
4	custom	0.5	3	408

But a query for any other publisher would return:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
5	standard	0.08	3	NULL

Answer 1

You seem to be looking for

SELECT *
FROM commission_rates cr_outer
WHERE publisher_id = 408
   OR publisher_id IS NULL AND kind <> 'standard'
   OR publisher_id IS NULL AND NOT EXISTS(
        SELECT *
        FROM commission_rates cr_inner
        WHERE cr_outer.merchant_id = cr_inner.merchant_id
          AND cr_inner.publisher_id = 408
      );