Solved

Help required implementing a search clause algorithm - SQL & Java

Posted on 2013-01-31
4
332 Views
Last Modified: 2013-04-23
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.

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 )

Open in new window


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_passion_code"

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.
0
Comment
Question by:Sreejith22
  • 3
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38842169
you can try something like this in SQL
Select refdata_content_code, Flag1 + Flag2 + Flag3 + Flag4 + Flag5 + Flag6 + Flag7 + Flag8 [Rank]
From ( Select  refdata_content_code,
case when refdata_drinkhabit_code = @refdata_drinkhabit_code then 1 else 0 end Flag1,
case when refdata_foodhabit_code = @refdata_foodhabit_code then 1 else 0 end Flag2,
case when refdata_language_code = @refdata_language_code  then 1 else 0 end Flag3,
case when refdata_location_code = @refdata_location_code then 1 else 0 end Flag4,
case when refdata_age_code = @refdata_age_code then 1 else 0 end Flag5,
case when refdata_sex_code = @refdata_sex_code then 1 else 0 end Flag6,
case when refdata_attribute_code = @refdata_attribute_code then 1 else 0 end Flag7,
case when refdata_profession_passion_code = @refdata_profession_passion_code then 1 else 0 end Flag8

from tbl_reference_databases Where 
refdata_drinkhabit_code = @refdata_drinkhabit_code
or refdata_foodhabit_code = @refdata_foodhabit_code
or refdata_language_code = @refdata_language_code 
or refdata_location_code = @refdata_location_code
or refdata_age_code = @refdata_age_code
or refdata_sex_code = @refdata_sex_code
or refdata_attribute_code = @refdata_attribute_code
or refdata_profession_passion_code = @refdata_profession_passion_code) as A
order by [Rank] desc

Open in new window

0
 

Author Comment

by:Sreejith22
ID: 38842411
will this work with sqlite database?
0
 
LVL 39

Expert Comment

by:appari
ID: 38842428
not familiar with SQLite syntax. just give it a try and see.
0
 
LVL 39

Expert Comment

by:appari
ID: 38842430
syntax i posted is of SQLServer. replace all @... with corresponding search parameter values.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video teaches viewers about errors in exception handling.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question