# Comparing values from two tables in a nested where clause.

oracle-11g-r2

This is a rather specific question. I'm working to create row restrictions in Business Objects and the customer wants records to fall within a certain range from another table. The problem is, you can only select one table. A work around I found was that I can select other tables in the WHERE clause.

For Example:

a.RE_UID = b.RE_UID


I have to use:

a.RE_UID IN
(select A.RE_UID
from
TABLE1 A
join
TABLE2 B
ON
A.RE_UID = B.RE_UID
)


Where I'm stumped is that I need compare off another table with a BETWEEN. Something like:

A.CL_FROM_SVC_DTE BETWEEN B.EFF_DTE AND B.END_DTE


If I just try like I did above I get errors that it expected another character after WHERE:

SELECT *
FROM
TABLE2
WHERE IN
(select A.CL_FROM_SVC_DTE
FROM TABLE1 A
JOIN TABLE2 B ON A.RE_UID = B.RE_UID
)
BETWEEN B.EFF_DTE AND B.END_DTE


SELECT T2.* FROM TABLE2 T2