Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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.

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
Sreejith22
Asked:
Sreejith22
  • 3
1 Solution
 
appariCommented:
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
 
Sreejith22Author Commented:
will this work with sqlite database?
0
 
appariCommented:
not familiar with SQLite syntax. just give it a try and see.
0
 
appariCommented:
syntax i posted is of SQLServer. replace all @... with corresponding search parameter values.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now