Lets say I have a table with these columns:
| id | mass | sequence | A | M | C |
0 100 AMMA 2 2 0
1 200 MMMC 1 3 1
I am interested in querying for the mass "220". Of course, none of these entries will register as a hit because they 100 =/= 220 and 200 =/= 220.
But I want to account for the possibility that each 'M' in the string can add '10' to the mass value, so for the first entry, I want it to also register for the masses 110, 120. For the second entry, I want it to register for the masses 210, 220, 230.
Something along the lines of:
SELECT *
FROM myTable
WHERE mass IN (mass, mass + M*10);
This query is not sufficient because it only accounts for the case where all M's add 10. I want to account for every M either adding or not adding 10.
I should note that the matches must be very precise (within an error window of 1). So 225 should not register as a hit, only:
209 < x < 211
219 < x < 221
229 < x < 231
Is this possible with a query?
Is this what you want?
where 220 between mass and mass + m * 10
You could also phrase this as:
where 220 - mass between 0 and m * 10
If you want an exact match, a recursive query is an alternative (this requires MySQL 8.0):
with cte as (
select id, mass, m from mytable
union all
select id, mass, m - 1 from cte where m > 0
)
select * from cte where mass + m * 10 = 220
If you want to allow a tolerance of 1, then just change the final query:
with cte as (...)
select * from cte where mass + m * 10 - 220 between -1 and 1