CODEWITHSUNDEEP
sqlsql-servert-sql
esac
esac

Reputation: 24685

How to find rows where a set of numbers is between two numbers?

I have a query which returns a set of numbers:

SELECT Sequence FROM Table1 WHERE Hash=2783342

Returns:

578
642
313

Now, I want to find all rows in the first table where any of that set of numbers is between two other columns. For the purpose of illustration, I am just picking 578, but I also want all of the rest:

SELECT * FROM Table1 WHERE 578 BETWEEN Sequence AND SequenceEnd

Upvotes: 1

Views: 201

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332541

Using a JOIN, but risks duplicates:

SELECT t.*
  FROM TABLE1 t
  JOIN (SELECT Sequence FROM Table1 WHERE Hash=2783342) x ON x.sequence BETWEEN t.sequence 
                                                                            AND t.sequenceend

Using EXISTS, no duplicate risk:

SELECT t.*
  FROM TABLE1 t
 WHERE EXISTS(SELECT NULL
                FROM TABLE1 x
               WHERE x.hash = 2783342
                 AND x.sequence BETWEEN t.sequence 
                                    AND t.sequenceend)

Upvotes: 2

Related Questions