Skip to main content
added 310 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

I think you want this, an anti-semijoin (also often called just "anti-join").

The "semi" because there is a join between a and b (in the subquery) but we only want and get rows from one table (b) in the result. The "anti" because we get rows from one table that do NOT have a related row in the other table.:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

I think you want this:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

I think you want this, an anti-semijoin (also often called just "anti-join").

The "semi" because there is a join between a and b (in the subquery) but we only want and get rows from one table (b) in the result. The "anti" because we get rows from one table that do NOT have a related row in the other table.:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

added 77 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

I think you want this:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

I think you want this:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

I think you want this:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

I think you want this:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;