Select from one table where range is held in columns belonging to another table

join;oraclequery

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.

Best Answer

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

Related Solutions

Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.

Sql-server – How to insert multiple returns of the select statement

If all students returned by the first select need to be combined with all faculties returned by the second select, then you just need to cross-join the two subsets and select from the resulting set. You can use either the classic comma join or the explicit CROSS JOIN syntax:

Comma join:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  (
    SELECT Student.StudentID 
    FROM dbo.Student 
    WHERE Student.Section = 'Section1'
  ) AS s
, (
    SELECT Faculty.FacultyID 
    FROM dbo.Faculty 
    WHERE Faculty.FirstName = @FacultyName 
    OR Faculty.LastName = @FacultyName
  ) AS f
;

CROSS JOIN:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  (
    SELECT Student.StudentID 
    FROM dbo.Student 
    WHERE Student.Section = 'Section1'
  ) AS s
  CROSS JOIN
  (
    SELECT Faculty.FacultyID 
    FROM dbo.Faculty 
    WHERE Faculty.FirstName = @FacultyName 
    OR Faculty.LastName = @FacultyName
  ) AS f
;

Those variations are using your subselects without any change. You could also rewrite the query and cross-join the tables first before filtering both in the same WHERE:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  dbo.Student AS s
, dbo.Faculty AS f
-- or: CROSS JOIN dbo.Faculty AS f
WHERE
  s.Section = 'Section1'
  AND (f.FirstName = @FacultyName 
    OR f.LastName = @FacultyName)
;

Note that you need to put the OR-ed Faculty filters in brackets in order to combine them with the Student filter properly. Without the brackets the result would not match the desired logic due to precedence rules for the logic operators in Transact-SQL: AND would be evaluated first, OR second, while you would want it to be the other way round, hence the brackets.