Apologies for the title, I am unable to define what I need in one sentence, partially because i do not fully understand the problem as this kind of SQL is over my head. This is an extension of a problem which I posted in another thread, which was solved here
Long story short, I have a table of padlocks. Each padlock has a specific set of locks that is needed to open it. Locks are in a separated table. Given a key type (which can open specific locks) I am searching for all the possible locks that can be unlocked with the key (note double locks like "KL, KL" cannot be unlocked with a key which only can unlock one "KL". So for I got all this working (thanks to @danblack)
So looking at the accepted answer, I have the following
table of padlocks
create table paddocks (
pd char(1),
sz enum('Small', 'Big'),
key (sz));
insert into paddocks values
('A', 'Small'),
('B', 'Small'),
('C', 'Small'),
('D', 'Small'),
('E', 'Small'),
('F', 'Big')
table of locks
create table locks (
pd char(1),
lk char(2),
key (pd,lk));
insert into locks values ('A', 'KL'),('A', 'OK'),('A', 'CZ'),('A', 'CZ'),
('B', 'OK'),('B', 'OK'),
('C', 'OK'),('C', 'CZ'), ('C', 'KL'),
('D', 'RO'),('D', 'CZ'), ('D', 'CZ'),
('E', 'OK'),('E', 'OK'), ('E', 'KL'), ('E', 'KL'),
('F', 'OK'),('F', 'OK'), ('F', 'CZ'), ('F', 'KL');
a key
create temporary table ky (
lk char(2),
key (lk));
insert into ky values
('KL'),('OK'),('CZ'),('OK'),('CZ')
The goal is to find all padlocks that are 1-lock away from being opened by a given key. In particular, I need padlock "D" to be displayed in the results, as it needs a "RO" lock, which the given key does not have, but it is "1-lock away" from being opened.
SELECT pd
FROM
(SELECT pd,
p.lk,
p.c,
count(ky.lk) AS kc
FROM
(SELECT pd,
locks.lk AS lk,
count(locks.lk) AS c
FROM paddocks
JOIN locks USING (pd)
WHERE sz = 'Small'
GROUP BY pd,
locks.lk) p
LEFT JOIN ky USING (lk)
GROUP BY pd,
lk) p
GROUP BY pd
HAVING count(pd) = sum(c <= kc)
This returns padlocks "A", "B" and "C" as they can all be opened by the given key. What is missing is padlock "D" which needs the "RO" lock. I guess, depending on the parameter, I would need either exact matches (like currently it is working), or exact matches + the ones with "1-lock away")
The previous answer has most of the calculations made. The final having clause needs to change for the updated requirement.
At the end of the query, sum(c) represents the number of locks a paddock needs. To see the amount of relevant keys a user has is sum(least(c,kc)). The least is there so that excess locks of one lock type cannot overflow to another lock type. With the original question. having sum(c) = sum(least(c,kc)) would have been an acceptable answer too.
To get the cases where you are short by one key, would be having sum(c) - 1 = sum(least(c,kc)).
To get the cases where the right amount of keys/locks or short by one key, the expression would be having sum(c) - 1 <= sum(least(c,kc)).
ref fiddle