I have two tables
table1
id from to
02 900 990
01 1005 1030
02 2190 3050
03 4150 4200
table2
id from to
01 1005 1030
02 2190 3000
02 3001 3050
03 4150 4175
What I am trying to do is for each row in table1, I want to check if it exists in table2 even if its in multiple rows in table2 but continuous.
In the above example for id 01 in table1 its easy to get id 01 in table2 as from and to are same. I got that.
But I also want for id 02 (the third row, not the first one). All from and to for id 02 in table1 are there in table2 but as they are in multiple rows I can’t get them.
Id 03 will not come as its not complete in table2
table1 can have multiple rows with same id but from and to range will be different and there will never be an overlap.
table2 can have the same id of table1 distributed in more than 2 rows also.
I am fetching rows from table1 and trying to find the from and to range in table2 so I have all the fields of the particular row from tabe1 in an array.
What I am expecting is just exists yes/no.
I am using PHP/ORACLE.
Any leads would be appreciated.
Thanks.
Below is the small working snippet if from and to are single row in table1 and table2.
$or1 = oci_parse($o_con, "select * from table1");
oci_execute($or1);
$count_1 = oci_fetch_all ($or1, $ow1 , 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
for ($i = 0; $i < $count_1; $i++)
{
$id = $or1[$i]['ID'];
$begin = $or1[$i]['FROM'];
$end = $or1[$i]['TO'];
$or2= oci_parse($o_con, "select * from table2 where from = $begin and to = $end and id= '$id'");
oci_execute($or2);
$count_2 = oci_fetch_all ($or2, $ow2 , 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
if ($count_2 == 0 ) { $color="red";} else { $color="green"; }
}