Help required implementing a search clause algorithm - SQL & Java
I need to perform search on a table by implementing an algorithm(either in the front end java code or in the back end sql query I write). For your reference, I have pasted the sql code for the table I am using.
From this table, I need to get the value corresponding to 'refdata_content_code' . The fields that would be included in WHERE clause is :
"refdata_drinkhabit_code"
"refdata_foodhabit_code"
"refdata_language_code"
"refdata_location_code"
"refdata_age_code"
"refdata_sex_code"
"refdata_attribute_code"
"refdata_profession_passio
The requirement with this search is, I need to have an algorithm such that, I could prioritize the results based on the number of fields included in the WHERE clause. If the WHERE clause involves all the 8 fields, then the result I get for 'refdata_content_code' could be prioritized as 1, and after that remove fields one by one in the WHERE clause.
Finally, if I get a result with a single field in the WHERE clause, that is the last priority result.
So, the first WHERE clause would include all the fields given above and after that, fields would be removed one by one in the ascending order of the fields given above.
I am not sure, whether this could be achieved with a single query or multiple queries. I do not even know what is the best approach I should use.
The front-end code I use is java.
Any help from the EE experts who could help me implement this algorithm would be much appreciated with points, surely.
If I need to clarify any portion of my question, kindly let me know.
CREATE TABLE "tbl_reference_databases" (
"id" integer primary key autoincrement,
"created" datetime DEFAULT NULL,
"modified" datetime DEFAULT NULL,
"refdata_content_code" varchar(10) DEFAULT NULL,
"refdata_profession_passion_code" varchar(10) DEFAULT NULL,
"refdata_attribute_code" varchar(8) DEFAULT NULL,
"refdata_sex_code" varchar(1) DEFAULT NULL,
"refdata_age_code" varchar(2) DEFAULT NULL,
"refdata_location_code" varchar(4) DEFAULT NULL,
"refdata_language_code" varchar(4) DEFAULT NULL,
"refdata_foodhabit_code" varchar(1) DEFAULT NULL,
"refdata_drinkhabit_code" varchar(1) DEFAULT NULL )From this table, I need to get the value corresponding to 'refdata_content_code' . The fields that would be included in WHERE clause is :
"refdata_drinkhabit_code"
"refdata_foodhabit_code"
"refdata_language_code"
"refdata_location_code"
"refdata_age_code"
"refdata_sex_code"
"refdata_attribute_code"
"refdata_profession_passio
The requirement with this search is, I need to have an algorithm such that, I could prioritize the results based on the number of fields included in the WHERE clause. If the WHERE clause involves all the 8 fields, then the result I get for 'refdata_content_code' could be prioritized as 1, and after that remove fields one by one in the WHERE clause.
Finally, if I get a result with a single field in the WHERE clause, that is the last priority result.
So, the first WHERE clause would include all the fields given above and after that, fields would be removed one by one in the ascending order of the fields given above.
I am not sure, whether this could be achieved with a single query or multiple queries. I do not even know what is the best approach I should use.
The front-end code I use is java.
Any help from the EE experts who could help me implement this algorithm would be much appreciated with points, surely.
If I need to clarify any portion of my question, kindly let me know.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
not familiar with SQLite syntax. just give it a try and see.
syntax i posted is of SQLServer. replace all @... with corresponding search parameter values.
ASKER