A few days ago I saw a question that among other things implied a problem similar to the one I am going to raise, I wanted to do it in a more general way, because I understand that the well-thought-out solution could serve as a reference to similar problems. Maybe for some of you the answer is trivial or obvious, but in my case, just when I chewed enough I found (at least I think so) that it was simpler than I thought. I raise it in Sql but it could be more of algorithms, the issue is that I found it more practical to be able to test the solutions.
Suppose the following example:
CREATE TABLE A ( NRO_DESDE INT, NRO_HASTA INT ) CREATE TABLE B ( NRO_DESDE INT, NRO_HASTA INT ) INSERT INTO A (NRO_DESDE, NRO_HASTA) VALUES (5, 8) INSERT INTO B (NRO_DESDE, NRO_HASTA) VALUES (1, 2), (4, 5), (5, 8), (6, 7), (7, 9), (4, 10), (9, 11) SELECT NRO_DESDE, NRO_HASTA FROM A; SELECT NRO_DESDE, NRO_HASTA FROM B;
A has a single value:
NRO_DESDE NRO_HASTA ========= ========= 5 8
NRO_DESDE NRO_HASTA ========= ========= 1 2 4 5 5 8 6 7 7 9 4 10 9 11
B represent sets of intervals, but of which we do not have all the values but we know the first and last element of each set, the idea is to compare the only set in
A with all of
B and determine if they share any element. As an example, the
B % registration
(4, 5) shares the
A , the
(1, 2) does not share any element, the
(7, 9) share the
7, 8 . The result would then be the records of
B that have elements shared with those of
A , it is not important to know what they are, just to know that there are, we can also assume that the number of elements in each set is relatively manageable. Do not worry about absences of primary keys is simply a conceptual example.
Note: The code is built in SQL Server but could be resolved in any "flavor" of SQL.