Avoid crossing weight range BETWEEN, with two columns? mysql

Question

I have two columns: amountFrom , amountTo in table shipping

Lets say I have these row data:

amountFrom | amountTo
-----------------------
0            15
16           30
31           50

Now I would like to add these three:

amountFrom | amountTo
-----------------------
15           22 (should fail, already exist (crosses range))
18           25 (should fail, already exist)
55           76 (should pass)

How can I make a correct sql query, that will run for each row i would like to insert, that will check if the "range" is available?

Example of what i tried

SELECT id FROM shipping WHERE amountFrom >= 15 AND amountTo <= 22

Above query returns no rows, which it should (if it was a correct query) since we dont want to make a new row with 15 and 22, as it will cross existing weight ranges

so, basically you ask how to check whether two intervals overlap? http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap — Karoly Horvath, Feb 02 '14 at 18:20

score 1 · Answer 1 · answered Feb 02 '14 at 18:37

You can try this (here with values 15 and 22) :

INSERT INTO t (amountFrom, amountTo)
 SELECT 15, 22
 WHERE NOT EXISTS (SELECT 1 FROM t WHERE 22 >= amountFrom AND 15 <= amountTo);

You can check the affected-rows value to see wether or not the row was actually inserted.

Gordon Linoff · Answer 2 · 2014-02-02T18:39:10.273

0

You don't have to do three separate inserts. You can do them all at once (at least with the data in your query).

The select statement to see which do not overlap is:

select t2.*
from table2 t2
where not exists (select 1
                  from table1 t1
                  where t1.amountFrom <= t2.amountTo and
                        t1.amountTo >= t2.amountFrom
                 );

Two ranges overlaps if one starts before the other ends, and the first ends after the other starts.

You put this into an insert as

insert into t1(amountFrom, amountTo)
    select t2.amountFrom, t2.amountTo
    from table2 t2
    where not exists (select 1
                      from table1 t1
                      where t1.amountFrom <= t2.amountTo and
                            t1.amountTo >= t2.amountFrom
                     );

EDIT:

If you want to do this one row at a time and prevent overlap in new rows as well:

insert into t1(amountFrom, amountTo)
    select t2.amountFrom, t2.amountTo
    from (select XX as amountfrom, YY as amountTo
         ) t2
    where not exists (select 1
                      from table1 t1
                      where t1.amountFrom <= t2.amountTo and
                            t1.amountTo >= t2.amountFrom
                     );

This will do the insert one step at a time with the overlap logic.

edited Feb 02 '14 at 18:39

answered Feb 02 '14 at 18:25

Gordon Linoff

1,242,037
58
646
786

what are you doing? there's only one table. – Karoly Horvath Feb 02 '14 at 18:27
@KarolyHorvath . . . I'm assuming that the three lines to be inserted, in table format in the OP, are actually a second table. – Gordon Linoff Feb 02 '14 at 18:28
you're probably wrong. anyway.. I think this assumes there are no overlaps in the second table. – Karoly Horvath Feb 02 '14 at 18:35
@KarolyHorvath . . . I agree (that is what I mean by it working for the data in the question). There is no example of "fail, exists as new row". – Gordon Linoff Feb 02 '14 at 18:37
There is only one table Gordon ? – Karem Feb 03 '14 at 13:26

Avoid crossing weight range BETWEEN, with two columns? mysql

2 Answers2