How to get Records using below scenario.

Question

Table1:

This is the table1 data it having IPNumber. It contain 50,000 Records.

Table1Image

Table2:

This is table2 data it having below columns like ip_from & ip_to. This table contain 2023184Recs.

Table2Image

Query:

I want to get the data from Table1 and Table2 based on condition that is IPNumber from Table1 check range

between Table2 ip_from, ip_to columns. How to get the data from above two table.

Please give any idea for above issue.

Anupam Singh · Answer

Seems you dont have primary ke in these table : how ever you can try below query for faster processing : SELECT Table_1.nameTable_2.address from Table_1 with(nolock)Table_2 with(nolock) where Table_1.IPno between Table_2.ipfrm and Table_2.ipto

Anupam Singh · Answer

Share complete table structure as i m not able to browse mentioned links. not sure but will try.

SWAROOP P · Answer

thanks... ya. same query iam applying here. But it takes 10-15 sec time to execute. Is there any other process with out using between. If you know any other ways plz let me know.

Jayakumar · Answer

The problem may be because we accessing the 2 million records directly from database table. So lets try with Temp Table concept in Database. Normally if u load Table in DB to DataTable on coding side then handled mean you will get fast response like that this Temp Table on DB queries. I will Show u an example.

SWAROOP P · Answer

thanks for reply.. ya. your query working fine but it takes lot of time bcz 2nd table having 2million records. Is there any way to write other than between? Present in my db have above mention records but day by day it will be increased.

Jayakumar · Answer

Hi Swaroop Here i give you an example. verify it. This is Table 1 This is Table 2 Query is: SELECT Table_1.nameTable_2.address from Table_1Table_2 where Table_1.IPno between Table_2.ipfrm and Table_2.ipto This output : If u got correct answer please let me know :)