CODEWITHSUNDEEP
sqlpostgresql
nn4l
nn4l

Reputation: 965

How to return default row if some values do not match?

I have a very simple table:

create table mydata (id integer, data character varying(255), a integer, b integer);
insert into mydata values 
(1, 'both a and b not found', null, null), 
(2, 'a=not found, b=20',      null,   20), 
(3, 'a=10, b=not found',        10, null), 
(4, 'a=10, b=20',               10,   20),
(... more rows with unique combinations of a and b);

I need a query that returns exactly one row for any value of a and b.

If both values for a and b are found, that shall select the specified row (id=4 in this example).

If these values are not found, but there is a row that matches the value of a and has a null in column b, that row shall define the default for this specific a and unknown b. Likewise, if b is found but not a and a row exists with the value of b and a null in column a, that is the default for that situation.

If no such default can be found but a row exists with a null in columns a and b, this row designates the general default.

Finally, in case the row does not exist either, zero rows are returned.

http://sqlfiddle.com/#!17/a5173/5

For now, I have solved the problem by some java code that runs several specific sql queries until it finds a match, but I guess there is a sql way to solve this problem. Please advise.

Upvotes: 1

Views: 1143

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

The most efficient method -- assuming an index on (a, b) -- is probably a more complicated query:

select d.*
from mydata d
where (a is null and b is null) or
      (a = 10 and b is null) or
      (a is null and b = 20) or
      (a = 10 and b = 10)
order by a nulls last, b nulls last
limit 1;

Expanding the where clause conditions should allow Postgres to use the index for fetching the rows rather than scanning the entire table.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247495

SELECT * FROM mydata
WHERE (coalesce(a, user_a), coalesce(b, user_b)) = (user_a, user_b)
ORDER BY a IS NULL, b IS NULL
LIMIT 1;

Here user_a and user_b are the values for which you query.

Upvotes: 5

sticky bit
sticky bit

Reputation: 37477

Hmm, maybe you can first filter the possible candidates and then use a CASE expression in a ORDER BY clause to sort the candidates by priority. Pick the top one by a LIMIT clause.

SELECT *
       FROM mydata
       WHERE a = :a
             AND b = :b
              OR a = :a
                 AND b IS NULL
               OR a IS NULL
                  AND b = :b
                OR a IS NULL
                   AND b IS NULL
       ORDER BY CASE
                  WHEN a = :a
                       AND b = :b THEN
                    1
                  WHEN a = :a
                       AND b IS NULL THEN
                    2
                  WHEN a IS NULL
                       AND b = :b THEN
                    3
                  WHEN a IS NULL
                       AND b IS NULL THEN
                    4
                END
       LIMIT 1;

:a and :b denote the values for a or b you search for.

Upvotes: 0

Related Questions