1

I have the following two tables, Table A and Table B.

What I'm trying to do is return a result set where:

  1. If the start position (only) in Table B is between the start and end position of any row in Table A, then I don't want that row from Table B to be in the result set.

  2. If any of the start and end positions in Table A are between any of the start and end positions of any row in Table B, then I don't want that row from Table A.

Here is Table A:

RID StartPos EndPos
7 45 77
7 118 130
7 197 212
7 218 235

Here is Table B:

RID StartPos EndPos
7 83 87
7 121 132
7 175 179
7 183 191
7 195 214
7 221 237

Here is my desired result set. You can see that 197 and 212 from Table A fall between the row in Table B that has 195 and 214, so that row from Table A isn't in the result set. You can also see that the start position 221 in Table B is between a range in Table A (218, 235), so that row from Table B isn't in the result set either.

RID StartPos EndPos
7 45 77
7 83 87
7 118 130
7 175 179
7 183 191
7 195 214
7 218 235

In sum, I want to exclude where:

  • TableA start pos and end pos are between any of table B's start pos and end pos.
  • TableB start pos is between any of table A's start pos and end pos.

Also, there are more than one RIDs in the table... I thought (and still think) I could get this done using a FULL join (or maybe using APPLY), but I'm getting a little stuck, which is why I'm asking you all for some help on it!

Here is the T-SQL to generate the two tables:

CREATE TABLE #TableA(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 45, 77)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 118, 130)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 197, 212)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 218, 235)


CREATE TABLE #TableB(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 83, 87)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 121, 132)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 175, 179)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 183, 191)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 195, 214)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 221, 237)


SELECT *
FROM #TableA

SELECT *
FROM #TableB
0

1 Answer 1

3

According to you question, I think this is what you want:

select * from #TableB b 
where not exists (
    select 1 from #TableA a 
    where b.StartPos between a.StartPos and a.EndPos
    and A.RID = b.RID
)
union
select * from #TableA a 
where not exists (
    select 1 from  #TableB b
    where a.StartPos between b.StartPos and b.EndPos
    and a.EndPos between b.StartPos and b.EndPos
    and A.RID = b.RID
)
;

Fiddle

10
  • Those two rows should be included because, only if the entire range in Table A falls within a range in Table B should the rows be excluded. 118-130 isn’t fully within any range in Table B. The same goes for 218-235. Therefore those rows should be in the result set. Does that help at all?
    – Mike S
    Jan 1, 2021 at 19:59
  • Without investigating it further, it may be sufficient to change or to an in 2leg of union. I have updated the answer Jan 1, 2021 at 20:14
  • Quick question. Would this solution take into account multiple different RIDs? I need to partition by the RID...
    – Mike S
    Jan 1, 2021 at 22:10
  • Just add the predicate a.RID =, b. RID to both EXISTS Jan 1, 2021 at 22:32
  • Awesome. Thank you!
    – Mike S
    Jan 2, 2021 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.