Find Exact match

[Arun Kumar]

The requirement is to receive 20 fields and match the fields against 20 field in data base and if a perfect match is found ..BINGO else need to reply to user ...no match found

On a high level its simple...but looking into the combinations 20 factorial = 2.43290201 × 10(18)

In reality there may not reach this figure but chances of the table grow int o Millions of records, is there, is there a way to find out the match with out a direct comparison

[Ulf Dittmer]

The number of combinations doesn't come into play. At worst, you'd have to do 20 SELECT statements to find out if all elements are contained in the table. If the table gets large, you'll want to make sure that the table has an index that can be (and actually gets) used for the SELECT.

[srinivas srinivasmeenavalli]

you'll want to make sure that the table has an index that can be (and actually gets) used for the SELECT

i agree with this.Indexing gives the best performance. You can have a dynamic query builder logic based on search criteria.

StringBuffer query="select * from dbtable/index where " 
if(f1 is not null){
 query.append(" f1 = " + f1 + " and ");
 }
if(f2 is not null){
 query.append(" f2 = " + f2 + " and ");
 }   
  ResultSet rs = executeQuery(quey);
 if( rs size > 0 )
{  
 return true;
 } 
else return false;
 

this would help you . you can prefer ibatis to create dynamic query . Ibatis suits this kind of requirements if you are coding in server side java application.

[David Newton]

Any ORM/JDBC will have a mechanism for creating queries.

Be *very* cautious about using the code above as given, however, unless you've already made sure the variables being used to create the query are cleaned of anything that can be used to create an SQL injection attack.

[Arun Kumar]

Thanks Guys

the problem here is not the select query, the table maintenance, if the table grows into say 40-50 thousand rows then the user who maintains this one will go mad

so i was hoping in suct a scenario is there any alogorithm or mathematical formula which will alow me to split the tables into multiple small tables and join then at run time to find a match

[David Newton]

What does the number of rows have to do with the query? We have tables with tens of millions of rows--the queries are the same.