0

I am trying to write a SELECT statement in Oracle SQL where I have a TABLE A with a "START" column and an "END" column which holds division numbers.

I want TABLE B to only extract those division numbers that are between columns "START" and "END" in table A

E.g

TABLE_A

ID | START_DIVISION | END_DIVISION
---+----------------+--------------
1  | 01             | 03
2  | 06             | 08
3  | 09             | 09

TABLE_B

DivisionNo | DivisionName
-----------+----------------------------
01         | London
02         | East Midlands
03         | West Midlands
04         | Yorkshire
05         | Lancashire
06         | Scotland
07         | South Wales
08         | North Wales
09         | Rep of Ireland

What I am after is something like this:

SELECT * FROM TABLE_B 
WHERE DivisionNo BETWEEN TABLE_A.START_DIVISION and TABLE_A.END_DIVISION

I'm assuming a JOIN statement may do this but I'm not quite sure how to do this in SQL.

Any help on this would be greatly appreciated.

1

In ANSI SQL and SQL Server you would just use an INNER JOIN. I don't know whether Oracle supports this, but I'd be surprised if it doesn't... No DDL, so typing off the top of my head:

SELECT * 
FROM table_b AS b
INNER JOIN table_a AS a ON b.DivisionNo BETWEEN a.start_division AND a.end_division

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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