Given the following tables:
CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(16))
INSERT INTO FeeTestClient (Name)
VALUES ('Test'), ('Test 2'), ('Test 3')
CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16))
INSERT INTO FeeTest (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
(1, 10, 'Default', 'asdf'),
(2, 15, 'Default', 'Default'),
(2, 20, 'Default', 'qwer'),
(2, 10, 'zxcv', 'asdf'),
(3, 20, 'Default', 'Default')
My goal is to select all FeeTestClient
elements, and pick the fee that is least default. The rules on a default fee are pretty simple: if Val2
is 'Default'
, then Val
cannot be anything except 'Default'
, and for each fee, we want the first one where Val
is not 'Default'
, or the first one where Val2
is not 'Default'
, otherwise we are guaranteed a Val = 'Default' AND Val2 = 'Default'
match.
The customer will only ever have one item that matches 'Default'/'Default'
, one item that matches 'Default'/____
and one item that matches ____/____
. (Though the last two rows may not exist.) If they have a ____/____
then they'll always have a 'Default'/____
, every customer will have a 'Default'/'Default'
. They can never have a ____/'Default'
— this is an invalid state on the application, and they can never have more than one of the same x/y
, this is enforced by a UNIQUE
constraint on the table.
It is possible (in the database) for the customer to have a 'Default'/a
, and a 'Default'/b
, but that is considered an invalid state in the application, and there is a test for that. (The user must delete one of the two.)
This is similar to a previous question of mine (Select all records, join with table A if join exists, table B if not), but much less pleasant. Because they're INT
(actually FLOAT
in the database, but the same issue applies) they're being aggregated together like I don't want.
I want to get a result of:
Id Name (No column name)
1 Test 10
2 Test 2 10
3 Test 3 20
I've tried:
SELECT Id, Name, COALESCE(f1.Fee, f2.Fee, f3.Fee)
FROM FeeTestClient
LEFT OUTER JOIN FeeTest AS f1 ON f1.ClientId = Id AND f1.Val <> 'Default' AND f1.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f2 ON f2.ClientId = Id AND f2.Val = 'Default' AND f2.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f3 ON f3.ClientId = Id AND f3.Val = 'Default' AND f3.Val2 = 'Default'
Which is ungodly slow on the live data-set, but returns the proper result (roughly 15 seconds, running the basic select on the data, without this selection of joins, is 7, plan is here), and I've also tried (by suggestion of Joe Obbish):
SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name
Which is just as slow (plan is here), and produces the wrong output (though if you COALESCE
the MAX
s then it works correctly). (Maybe worse.)
I'm at a loss, and writing these queries has been very painful, so any advice on building the desired output is appreciated.
I've provided the actual plans, but they differ very heavily from the test ones (this MCVE is to demonstrate the result I want, answers are not obligated or even expected to make any performance claims), it seems. Please disregard the extra joins shown in the plans above - they are unimportant to the example.
As far as distribution, in the live data-set, 1.5% only have a 'Default'/'Default'
, 44.9% have 'Default'/___
and 'Default'/'Default'
, and 53.6% have all three.