# How can I exclude rows from two tables whose values are encompassed by each other?

Posted on

### Question :

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
``````

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